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;

SOLUTION

nmcdermaid

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jaspreet_bajaj

i dont think you can do it in one single query

try to use the query given by nmcdermaid and store data in temp table if you want

jaspreet

ASKER CERTIFIED SOLUTION

GreymanMSC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robert Berke

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