Hi there. Is there, please, a way to quickly return the number of UNIQUE values returned by a query. For example in a table I have:

Name.........Qty
Paul.............4
Denis...........6
Paul.............3
Joe..............9
Paul.............2
Joe..............7

Can I make a query that will count the number of unique record in the Name fiels only indenpendant of the Qty field. In this case it should return 3.

At this point I need to do two queries 1) GroupBy Name and 2) Count record in the first query using Count. Maybe the solution resides in a subquery but subqueries are not my strength.

Thank you for you time.

Daniel

__________________
Daniel

You're right about using Group By to get the unique instances.

To get the count of each unique instance, just add a Count field to your query. Make sure that the field you use will always have a value other than Null (e.g. the ID field for the record) otherwise you won't know how many records have null values in the field you are grouping by.

To get the count of the number of unique instances you can use the Domain Aggregate function DCount. E.g. DCount("*","queryname") where queryname is the query with the Group By in it. Use the "*" as the first parameter if you want to count records with nulls... If you use a specific field DCount doesn't count instances where the field is null.

You can use DCount as a field in the query where you're doing the Group By. For your example, assuming the query name is "qryCount" and the table name is "tblNames" the SQL would be:

SELECT tblNames.Name, Count(tblNames.Name) AS CountOfName, DCount("*","qryCount") AS TotalCount
FROM tblNames
GROUP BY tblNames.Name;

Note: Using this SQL, TotalCount will include a count if the Name field is Null but CountOfName won't tell you how many records have a Null in Name. If you want CountOfName to tell you how many Null names you have, use a different field for Count() -- a field that will always have a value. If you don't want to count Null names in TotalCount, change to DCount("Name","qryCount").

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Thanks Randall but did you try your solution with an Access table? It doesn't work for me!! When I run :

SELECT tblDupCities.CityName, Count(tblDupCities.CityID) AS TotalCities, DCount("*","qryCount") AS TotalCount
FROM tblDupCities
GROUP BY tblDupCities.CityName;

I ger an error message: Query input must contain contain one table or query.

How in the world can I use qryCount inside a query called itself qryCount?

I read your mail 5 times and I don't get it. Yes yes I know that I should not count a field with a null because the count will be wrong yes I know that!

Please Randall, clarify!! Please?

Daniel

Daniel,

Sorry for the struggles.

Here is exactly what I did using Access 2003...
I created a new query and selected my table (in my case Table1). I selected two fields (in my case Field1 and Field2). I changed the query to show totals and set "Field1" to "Group By" and "Field2" to "Count". I made the name of the "Field2" "CountOfName" by adding "CountOfName: " before "Field2". I saved the query as "qryCount" and tested it to see if I was getting the correct grouping and counting.

I then added a field to the query
TotalCount: DCount("*","qryCount")
I changed the Total setting for that to "Expression" (even though it can be left as "Group BY").
I ran the query. It worked. I saved the query and ran it again. It worked.

I also did this in Access 2000. Everything worked fine for me.

The trouble is I don't know exactly what you did to use the SQL statement sample I sent and you didn't indicate which version of Access you're using.

For example, did you copy my example, change the names and save the query as "qryCount"? If you haven't saved the query, the DCount function won't work. But the error I get if I try not saving the query first is that "qryCount" isn't found.

When I copy your SQL statement to a new query in my database, the error I get is "blah, blah... cannot find table tblDupCities".

The point is, I can't seem to reproduce the error you're getting.

Is the select statement you put in your post a copy from your query or did you retype it? If you retyped it you may have corrected something, e.g. "FROM" instead of "FORM" or some such. By the way, I tried this little typo too. Once again the error is "table not found"

Is the name of your table actually "tblDupCities"? Or is tblDupCities actually another query?

One last thought is that your database is corrupt. Try a compact and repair. Try relinking tables (in case tblDupCities is a linked table). As a last resort, try creating a new, very simple database and try the solution I proposed in that simple database. If that doesn't work, somebody is going to tell me how to reproduce your error so I can debug it more.

Sorry I can't think of any other way to be of more help.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

Ah ah Randall!

Maybe this is what happened I didn't save the query before (although I thought I did). The difference I did now was not only to save it but to close it and run it at least once. I read somewhere that when you run a query Access compiles it to optimize it. Now it existed in Access internal tables.

So within the query qryCount I have a reference to qryCount using DCount .... very very clever Randall!

It worked for me now! Thanks a bunch. This is very clever indeed. I wouldn't thought of that for sure!!

Daniel

Daniel,

Glad it worked!

It was clever. But we should be sure it works...

You reminding me that running a query optimizes it makes me wonder if I was getting the correct results. You might want to try adding a few records with unique Group By values and make sure the TotalCount field is correct. It should be. But just to be sure...

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org