Click here to Skip to main content
15,668,517 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone!

I would like to start by saying thanks to everyone who takes some time to view this thread and try to help.

I will be brief, and cut to the point:

I have a table in MS Access, it contains 2 columns of interest- County, and TGTE (Type Of Geothermal Energy ). Column TGTE is of type VARCHAR and it can have 1 of two values, to make it easier let's say it is either L or H.

I need to create SQL query that shows a result which is described bellow ( code tags are for clearer look ):

Bellow is the part of the table:

County | TGTE | ... other fields |  
First  |   L  |
First  |   L  |
First  |   H  |
Second |   H  |
Third  |   L  |

I need a resulting query that shows the count of distinct TGTE in every County like this:

County | TGTE = L | TGTE = H |  
First  |    2     |     1    |
Second |    0     |     1    |
Third  |    1     |     0    |

How can I create query that displays the desired result described above ?
Mohibur Rashid 5-Aug-13 20:55pm    
try stored procedure

I have the query like bellow, and it works:

SELECT  County
         ,sum( switch( ТGTE = 'L', 1, TGTE = 'H', 0 ) ) as [L_Count]
         ,sum( switch( ТGTE = 'H', 1, TGTE = 'L', 0 ) ) as [H_Count]
 FROM    MyTable

Everything works fine, when I run it through ADO and MS Access 2007.
Share this answer
Couple of options... either create a query to group by County and TGTE and count the ID field, then run a crosstab query over that...


Create a subquery for each of the two types of TGTE, group by county and where clause on the TGTE, then have a main query to return the County, attached to the two subqueries (by a left join) to get TGTE column one and two.
Share this answer
MyOldAccount 6-Aug-13 19:02pm    
I am inexperienced with SQL.
Although I vaguely understand what you're talking about, I could really use a code snippet, or a pseudo code.
Thank you for your suggested answer!
Share this answer
MyOldAccount 6-Aug-13 19:04pm    
This is interesting, I will try it out and report my results as soon as possible!
Thank you for your suggested solution!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900