Return a count of unique entries?

I am hoping someone can help me here.
I am trying to return a unique count of Applications no's
by Department from a union query containing 10000+
records.
Overall, there are 30 Departments and 1900 Applications
No's in my DB. Applications No's are always allocated to
one Department only.
I want to return a list of Departments that shows an
unique count of Applications No's

For example...

Dept ApplicationNo
A 122
A 122
A 234
B 345
B 345

This would work out to be..

Dept CountofApplicationNo
A 2
B 1

Can this be done?

Thanks

Tony

Brian Camire wrote:

You might try something like this:

SELECT
Dept,
Count(*) AS CountofApplicationNo
FROM
(SELECT DISTINCT
Dept,
ApplicationNo
FROM
YourTable)
GROUP BY
Dept

Access 97 and earlier don't support subqueries in the FROM clause. If
you're using one of those, you'll need to save the SELECT DISTINCT subquey
as a separate query, and use the saved query (instead of the SELECT DISTINCT
subquery) in the FROM clause above.


To clarify the issue of subqueries for those still using
A97, it does allow subqueries in the FROM clause (including
JOINs). It does require a different syntax though:

FROM
[SELECT DISTINCT
Dept,
ApplicationNo
FROM
YourTable]. AS T

The silly dot after the ] is required.

--
Marsh
MVP [MS Access]

You're right! I discovered the same also applies to Access 95. For some
reason I didn't think this was added until Access 2000.

Thanks for pointing this out.

"Marshall Barton" wrote in message
...
Brian Camire wrote:

You might try something like this:

SELECT
Dept,
Count(*) AS CountofApplicationNo
FROM
(SELECT DISTINCT
Dept,
ApplicationNo
FROM
YourTable)
GROUP BY
Dept

Access 97 and earlier don't support subqueries in the FROM clause. If
you're using one of those, you'll need to save the SELECT DISTINCT
subquey
as a separate query, and use the saved query (instead of the SELECT
DISTINCT
subquery) in the FROM clause above.


To clarify the issue of subqueries for those still using
A97, it does allow subqueries in the FROM clause (including
JOINs). It does require a different syntax though:

FROM
[SELECT DISTINCT
Dept,
ApplicationNo
FROM
YourTable]. AS T

The silly dot after the ] is required.

--
Marsh
MVP [MS Access]