asked on

IMPOSSIBLE? select state, count(distinct city), count(distinct BirdsSeen) from transactionFile

IMPOSSIBLE? select state, count(distinct city), count(distinct BirdsSeen) 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 an hour and have seen all sorts of "accepted answers" that don't work exactly right for the general case.

They either are syntactically incorrect or they give other error messages.
I think the following should work, but it doesn't.

SELECT toplvl.state,
Count(*) FROM (
select distinct lowlvl.city
FROM trans as lowlvl WHERE lowlvl.state = toplvl.state
) ,
Count(*) FROM (
select distinct lowlvl.birdseen
FROM trans as lowlvl WHERE lowlvl.state = toplvl.state
)
FROM trans as toplvl
GROUP BY toplvl.State;

Rberke
----------------------------------------
p.s. I want to count BOTH BirdsSeen and city names. In fact, in some case I want to count 4 or 5 other columns in the same query. For the special case where I only want to count 1 field, there is a simple solution:

SELECT state,Count(toplvl.city) as KntCity
FROM (select distinct lowlvl.city,lowlvl.state FROM trans as lowlvl) as toplvl
GROUP BY toplvl.State;

which was posted in https://www.experts-exchange.com/questions/21398241/IMPOSSIBLE-select-count-distinct-city-state-from-transactionFile.html#13839783

kmslogic

SELECT toplvl.state,
Count(*) FROM (
select distinct lowlvl.city
FROM trans as lowlvl WHERE lowlvl.state = toplvl.state
) as CityCount,
Count(*) FROM (
select distinct lowlvl.birdseen
FROM trans as lowlvl WHERE lowlvl.state = toplvl.state
) as BirdCount
FROM trans as toplvl
GROUP BY toplvl.State;

ASKER CERTIFIED SOLUTION

kmslogic

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

Yep, I've thought that was the case all along, but its nice to get confirmation for another expert.

By "unrolling" your query I get the following which works, but oh my goodness, what a pain!

You'll get the points unless someone comes up with a better solution.

---- access replacement for 'select state, count(distinct city), count(distinct BirdsSeen) from trans' -----------
SELECT QMaster.state, Q1.KntCity, Q2.KntBird
FROM (

(SELECT DISTINCT trans.state
FROM trans
) as qmaster

LEFT JOIN
(SELECT state, Count(toplvl.city) AS KntCity
FROM [select distinct lowlvl.city,lowlvl.state FROM trans as lowlvl]. AS toplvl
GROUP BY toplvl.State
) as q1
ON QMaster.state = Q1.state)

LEFT JOIN
(SELECT toplvl.state, Count(toplvl.birdsSeen) AS KntBird
FROM [select distinct lowlvl.birdsSeen,lowlvl.state FROM trans as lowlvl]. AS toplvl
GROUP BY toplvl.state
) as q2
ON QMaster.state = Q2.state;

Robert Berke

ASKER

Guess nobody has a better way of doing this in access.

It may be an ugly solution, but it does meet my original request for doing everything in single query.

Do here are your points.