Access newbie needs help - Distinct, Group By Query.

[steveyell]

Hi there,

I have a DB with 90,000 records and I want to count the number of distinct postcodes (fieldname=post code) 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!

[Rabbit]

Use your distinct query in another query.

[steveyell]

Sorry - I am a total newbie - how do you run a query within a query?

[brightshadow]

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.

[steveyell]

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.

[brightshadow]

[Completely insane post edited out!]

See post below. [Mods, feel free to remove this one!]

[brightshadow]

Oh...Wait a minute. I think -maybe- I was overcomplicatin g things.

How about this?

SELECT DISTINCT Spare2, COUNT(postcode)
FROM Table1
GROUP BY Spare2

[steveyell]

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?

[brightshadow]

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?

[Rabbit]

You can either do:
[Code=sql]
SELECT Count(PostCode) AS CountOfPostCode , Spare2
FROM (SELECT DISTINCT PostCode, Spare2 FROM TableName) AS x
GROUP BY Spare2;
[/Code]

Or

Query1:
[Code=sql]
SELECT DISTINCT Postcode, Spare2
FROM TableName;
[/Code]

Query2:
[Code=sql]
SELECT Count(PostCode) As CountOfPostCode , Spare2
FROM Query1
GROUP BY Spare2;
[/Code]

[steveyell]

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!

[Rabbit]

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.