Grouping a Count of Unique Records - Going Round in Circles :(

[RFJ]

The following is a bit of code used to count unique records. It works as a
want it to (as far as it goes).

SELECT Count(*) AS [Number]
FROM [SELECT DISTINCT [Org_Name] FROM [1]]. AS [%$##@_Alias];

It returns :

Number
23

What I want to do is to use that code but to count the number of unique
records that match another field. So the output from the query would be

Size Number
1 6
2 3
3 8
4 6

The table name is Band
The field name is Size

Can SKS help

TIA

Rob

 

[Jeff Boyce]

It sounds like you want to group by size and count records. Not sure on the
SQL statement, but I can "see" the query design grid with a Totals query,
Group By on [Size] and Count of [YourRowID].

 

[RFJ]

Jeff, tx for the reply. That approach seemed to count all the records, not
just the unique ones. I've copied again the code (before Access started
adding bits I did not understand!) in case it's easier to comprehend.

SELECT Count(*) FROM
(SELECT DISTINCT [Org_Name]
FROM [1])

Just to confirm I'm trying to count the 23 unique records that emerge from
that code according to a size criteria (all part of the same query). So the
output would look like

Size No of occurences
1 6
2 3
3 8
4 6

The table name is Band
The field name is Size

Any further help would be much appreciated.

Rob

 

[John Spencer (MVP)]

Access plus Jet would be something like the following, although the use of [1]
as the table (or query) name may give you problems.


SELECT Size, Count(*) FROM
(SELECT DISTINCT [Org_Name], Size
FROM [1])
GROUP BY Size

Access would normally prefer something like the following.

SELECT Temp.Size, Count(Temp.Org_Name)
FROM [SELECT DISTINCT Size, Org_Name
FROM YourTable]. as Temp
GROUP BY Temp.Size

This structure only works if you don't have spaces or other special characters
in your field and table name. Otherwise, create a query that returns unique
(DISTINCT) combinations of Size and whatever you are counting and then use that
as the source query (the FROM).

Jeff, tx for the reply. That approach seemed to count all the records, not
just the unique ones. I've copied again the code (before Access started
adding bits I did not understand!) in case it's easier to comprehend.

SELECT Count(*) FROM
(SELECT DISTINCT [Org_Name]
FROM [1])

Just to confirm I'm trying to count the 23 unique records that emerge from
that code according to a size criteria (all part of the same query). So the
output would look like

Size No of occurences
1 6
2 3
3 8
4 6

The table name is Band
The field name is Size

Any further help would be much appreciated.

Rob

It sounds like you want to group by size and count records. Not sure on the
SQL statement, but I can "see" the query design grid with a Totals query,
Group By on [Size] and Count of [YourRowID].