By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,483 Members | 3,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

Access newbie needs help - Distinct, Group By Query.

P: 6
Hi there,

I have a DB with 90,000 records and I want to count the number of distinct postcodes (fieldname=postcode) and then group them by area(fieldname=Spare2).

The ideal output would be two columns, one column with area(Spare2), and the other column a count of the unique postcodes for that area.

Any help would be appreciated, as I can get a list of distinct postcodes (around 7,500), but can't work out how to then group them by area, and display them.

Many thanks, and Happy Christmas by the way!
Dec 21 '07 #1
Share this Question
Share on Google+
11 Replies


Rabbit
Expert Mod 10K+
P: 12,366
Use your distinct query in another query.
Dec 21 '07 #2

P: 6
Sorry - I am a total newbie - how do you run a query within a query?
Dec 21 '07 #3

brightshadow
P: 10
To embed a query within a query, just use parentheses.

SELECT This, That, (SELECT TheOtherThing FROM Here WHERE This = 'That') As 'Result'
FROM ThisPlace
WHERE Something = 'Nothing'

You can place an entire query within another query that way, as long as it only returns one value.
Dec 21 '07 #4

P: 6
Thanks for that BrightShadow - it's close but I want the second column to be simply a total of the distinct postcodes within that area, so Area 1 has 1200 distinct postcodes, Area 2 has 1100 etc.

I tried your query and as you say it lists all the distinct postcodes and then has a column listing what area it is in - therefore there are 7500 records.

The query I need would only have 5 records - a total of the distinct postcodes for each of the 5 areas.

Thanks for your help once again, it is appreciated.
Dec 21 '07 #5

brightshadow
P: 10
[Completely insane post edited out!]

See post below. [Mods, feel free to remove this one!]
Dec 21 '07 #6

brightshadow
P: 10
Oh...Wait a minute. I think -maybe- I was overcomplicating things.

How about this?

SELECT DISTINCT Spare2, COUNT(postcode)
FROM Table1
GROUP BY Spare2
Dec 21 '07 #7

P: 6
We're close now, but the figures don't add up - there are now 5 areas listed in the first column (from spare2) but the totals in the second column (titled Expr1001) are incorrect.
It says Area one has 12977 distinct postcodes, but there are only 7500 distinct postcodes in total in the DB.

Any ideas?
Dec 21 '07 #8

brightshadow
P: 10
Ohh! Yeah, I see what it's doing there. It's counting the records per area but not the distinct records.

What you'd need is something more along the lines of a subquery that returns a distinct list of areas and postcodes (like the original one I gave you) and put that inside a query that just counts the postcodes grouped by area.

I'm honestly better at this with MS SQL than Access; Access's SQL implementation is a bit less flexible and I'm totally unfamiliar with the "design view" query method, as it really doesn't make a lick of sense to me. Can anyone that's a bit better with Access SQL chime in and point out how this should be structured?
Dec 21 '07 #9

Rabbit
Expert Mod 10K+
P: 12,366
You can either do:
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(PostCode) AS CountOfPostCode, Spare2
  2. FROM (SELECT DISTINCT PostCode, Spare2 FROM TableName) AS x
  3. GROUP BY Spare2;
  4.  
Or

Query1:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Postcode, Spare2
  2. FROM TableName;
  3.  
Query2:
Expand|Select|Wrap|Line Numbers
  1. SELECT Count(PostCode) As CountOfPostCode, Spare2
  2. FROM Query1
  3. GROUP BY Spare2;
  4.  
Dec 21 '07 #10

P: 6
Thanks Rabbit

That was exactly what I was looking for. I used the first combined query and got the right results.

I'm glad I found this forum - it's really helpful.

Happy Christmas folks!

Thanks also to brightshadow for the help!
Dec 21 '07 #11

Rabbit
Expert Mod 10K+
P: 12,366
Thanks Rabbit

That was exactly what I was looking for. I used the first combined query and got the right results.

I'm glad I found this forum - it's really helpful.

Happy Christmas folks!

Thanks also to brightshadow for the help!
Not a problem.

They're both basically the same thing. The second approach was what I was suggesting the first time around because it's easier to understand the two queries separately. Most people have trouble with subqueries at first. It's a bit of a learning curve.
Dec 21 '07 #12

Post your reply

Sign in to post your reply or Sign up for a free account.