Robert Berke
asked on
IMPOSSIBLE? select count(distinct city), state from transactionFile
I know MS Access doesn't support this use of "distinct" and I know how to simulate it with multiple queries.
But, trying to do it with a single query seems impossible. I have researched EE for over and hour and have seen all sorts of "accepted answers" that don't work exactly right.
They either are syntactically incorrect or they give an error message that only a
SELECT Count(*) FROM (select distinct a2.city
FROM trans as a2 WHERE a2.state = "ga") AS knt ,a1.state
FROM trans as a1
GROUP BY a1.State;
But, trying to do it with a single query seems impossible. I have researched EE for over and hour and have seen all sorts of "accepted answers" that don't work exactly right.
They either are syntactically incorrect or they give an error message that only a
SELECT Count(*) FROM (select distinct a2.city
FROM trans as a2 WHERE a2.state = "ga") AS knt ,a1.state
FROM trans as a1
GROUP BY a1.State;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry, I oversimplified my question. But, in fairness to greymanMac, he DID answer my question completely and accurately.
Also, nmcdermaid's solution required two queries, but it is also correct, so he gets some points also
The more complex question:
"IMPOSSIBLE? select state, count(distinct city), count(distinct BirdsSeen) from transactionFile"
is posted at https://www.experts-exchange.com/questions/21398428/IMPOSSIBLE-select-state-count-distinct-city-count-distinct-BirdsSeen-from-transactionFile.html
Also, nmcdermaid's solution required two queries, but it is also correct, so he gets some points also
The more complex question:
"IMPOSSIBLE? select state, count(distinct city), count(distinct BirdsSeen) from transactionFile"
is posted at https://www.experts-exchange.com/questions/21398428/IMPOSSIBLE-select-state-count-distinct-city-count-distinct-BirdsSeen-from-transactionFile.html
try to use the query given by nmcdermaid and store data in temp table if you want
jaspreet