• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5676
  • Last Modified:

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 http://www.experts-exchange.com/Databases/MS_Access/Q_21398241.html#13839783

0
rberke
Asked:
rberke
  • 2
  • 2
1 Solution
 
kmslogicCommented:
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;
0
 
kmslogicCommented:
Ah sorry you can't do that with multiple FROM clauses.  In general to solve this sort of a problem I create multiple queries and then join them together in access.

QCounts Query:

SELECT QMaster.state, Q1.KntCity, Q2.KntBird
FROM (QMaster LEFT JOIN Q1 ON QMaster.state = Q1.state) LEFT JOIN Q2 ON QMaster.state = Q2.state;

QMaster Query:

SELECT DISTINCT trans.state
FROM trans;

Q1 Query:

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

Q2 Query:

SELECT toplvl.state, Count(toplvl.birdseen) AS KntBird
FROM [select distinct lowlvl.birdseen,lowlvl.state FROM trans as lowlvl]. AS toplvl
GROUP BY toplvl.state;




You could "unroll" the queries and put them together by expanding out Q1, Q2 and QMaster in the appropriate place in the QCounts query


Kelly
0
 
rberkeAuthor Commented:
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;
0
 
rberkeAuthor Commented:
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.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now