Return a count of unique entries?

T

Tony

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
 
K

Ken Snell [MVP]

SELECT Dept, Count([ApplicationNo])
FROM TableName
GROUP BY Dept, ApplicationNo;
 
T

Tony

Ken,

Thanks for the quick reply.
It works to a certain extent - the query returns the
correct total amount of unique applications (1731) but it
is not grouped uniquely by Department. Displayed result
looks something like this...

Department CountOfApplicationRefNo
A 1
A 1
A 3
A 2
A 3
B 1
B 3


Any thoughts?

Thanks

Tony
-----Original Message-----
SELECT Dept, Count([ApplicationNo])
FROM TableName
GROUP BY Dept, ApplicationNo;


--

Ken Snell
<MS ACCESS MVP>

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
Click to expand...


.
Click to expand...
 
B

Brian Camire

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.

Tony said:
Ken,

Thanks for the quick reply.
It works to a certain extent - the query returns the
correct total amount of unique applications (1731) but it
is not grouped uniquely by Department. Displayed result
looks something like this...

Department CountOfApplicationRefNo
A 1
A 1
A 3
A 2
A 3
B 1
B 3


Any thoughts?

Thanks

Tony
-----Original Message-----
SELECT Dept, Count([ApplicationNo])
FROM TableName
GROUP BY Dept, ApplicationNo;


--

Ken Snell
<MS ACCESS MVP>

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
Click to expand...


.
Click to expand...
Click to expand...
 
M

Marshall Barton

Brian said:
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.
Click to expand...


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.
 
B

Brian Camire

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 said:
Brian said:
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.
Click to expand...


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.
Click to expand...
 
T

Tony

Brilliant Brian,
Worked a treat and well done - thanks for your help

Tony
-----Original Message-----
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.

Ken,

Thanks for the quick reply.
It works to a certain extent - the query returns the
correct total amount of unique applications (1731) but it
is not grouped uniquely by Department. Displayed result
looks something like this...

Department CountOfApplicationRefNo
A 1
A 1
A 3
A 2
A 3
B 1
B 3


Any thoughts?

Thanks

Tony
-----Original Message-----
SELECT Dept, Count([ApplicationNo])
FROM TableName
GROUP BY Dept, ApplicationNo;


--

Ken Snell
<MS ACCESS MVP>

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
Click to expand...
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



.
Click to expand...
Click to expand...


.
Click to expand...
 
K

Ken Snell [MVP]

Sorry.... that's what I get for typing off the top of my head:

SELECT Dept, Count([ApplicationNo])
FROM TableName
GROUP BY Dept;

--

Ken Snell
<MS ACCESS MVP>

Tony said:
Ken,

Thanks for the quick reply.
It works to a certain extent - the query returns the
correct total amount of unique applications (1731) but it
is not grouped uniquely by Department. Displayed result
looks something like this...

Department CountOfApplicationRefNo
A 1
A 1
A 3
A 2
A 3
B 1
B 3


Any thoughts?

Thanks

Tony
-----Original Message-----
SELECT Dept, Count([ApplicationNo])
FROM TableName
GROUP BY Dept, ApplicationNo;


--

Ken Snell
<MS ACCESS MVP>

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
Click to expand...


.
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

< groan >

This is not my afternoon.... ignore my feeble attempts today, and obviously
go with what worked....! (Marsh's and Brian's replies, that is!)
--

Ken Snell
<MS ACCESS MVP>


Ken Snell said:
Sorry.... that's what I get for typing off the top of my head:

SELECT Dept, Count([ApplicationNo])
FROM TableName
GROUP BY Dept;

--

Ken Snell
<MS ACCESS MVP>

Tony said:
Ken,

Thanks for the quick reply.
It works to a certain extent - the query returns the
correct total amount of unique applications (1731) but it
is not grouped uniquely by Department. Displayed result
looks something like this...

Department CountOfApplicationRefNo
A 1
A 1
A 3
A 2
A 3
B 1
B 3


Any thoughts?

Thanks

Tony
-----Original Message-----
SELECT Dept, Count([ApplicationNo])
FROM TableName
GROUP BY Dept, ApplicationNo;


--

Ken Snell
<MS ACCESS MVP>

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



.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count unique with multiple criteria 3
Counting unique items based on date 19
Table to pick out most common entries and count occurences of each 4
counting problem 4
Can "countifs" be utilized to return a count of unique values? 1
count unique with conditions 8
count unique in one column based on two other columns 4
Unique records formula & zero entries 9

Top