Unique Count - Syntax Problem

[RFJ]

I've been watching other posts looking for advice on counting unique records
(ie taking out repeats in a field) but I just can't get the syntax to work.

The sql code for the total count follows. Is there SKS who could let me know
what the code change is to strip out the repeats.

TIA

Rob


SELECT Survey_Data.Survey_Job_Level, Count(Survey_Data.Organisation_Name) AS
CountOfOrganisation_Name
FROM Survey_Data
GROUP BY Survey_Data.Survey_Job_Level
HAVING (((Survey_Data.Survey_Job_Level)="Manager"));

 

[Tom Ellison]

Dear Rob:

In my experience, different people define "repeats" and "unique"
differently. Are there rows in Survey_Data that are exact duplicates
in every column? Or are there selected columns which, when identical,
make up a set that defines duplication to you?

I think perhaps you want to count how many different values of
Organisation_Name there are for each Job_Level. Is that the case?

A query like this may be useful:

SELECT Count(Organisation_Name) AS CountOfOrganisation_Name
FROM (SELECT DISTINCT Survey_Job_Level, Organisation_Name
FROM Survey_Data)
WHERE Survey_Job_Level = "Manager"

Compared to your query I have dropped showing the Survey_Job_Level,
since it will be "Manager" all the time and added a sub-query that
removes duplicate Organisation_Name values from the set being counted.
I'm guessing that may be what you want. Also, the HAVING becomes
WHERE since you're not filtering by any aggregate.

I recommend you run the inner query to show you how this works:

SELECT DISTINCT Survey_Job_Level, Organisation_Name FROM Survey_Data

Counting from this is what I think you were wanting.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Rob:

Congratulations to me for guessing well. I feel lucky today!

Actually, as I worked my way through an answer, it became apparent
what you wanted, but it does take an extra minute or two to figure
this out. So (no offense, please) if posters here are specific and
detailed about their questions, they are more likely to get the right
answer, and will save us some time.

In the Design Grid, the top row named "Field:" you can specify how to
name a column. Put in the desired column name, then a colon and a
couple of spaces. Insert this before what is already shown in that
box. See if that does it for you.

In the SQL, all you need to do is change what follows AS. You could
change that, then go back and look at the Design View to see how it
represents that. You could make the same change to Design View to
change the SQL, and get the same result.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[RFJ]

Tom,

Excellent. You'd guessed correctly in terms of what I wanted and the code is
working fine. Just one final question - the column heading of the query
comes out as CountOfOrganisation_Name. How do I simplify it as the column
doesn't appear in the Design View of the query which is where I'd normally
make the change.

TIA

 

[RFJ]

Tx a lot <BG>

I take your point about posters being as specific as possible - no offence
taken.

Rob