# Thread: Count Different Items In One Querry (Access 2000)

1. ## Count Different Items In One Querry (Access 2000)

I have a simple table that records how many widgets and gadgets 5 different people make each day (just four fields: date, name, number of widgets, number of gadgets made). At the end of the week, I want to count how many w and g each person made in the week. Is it possible to do in one simple query ? Thanks !

2. ## Re: Count Different Items In One Querry (Access 2000)

Hans... <img src=/S/shy.gif border=0 alt=shy width=15 height=15>

I hesitate to say this... but... Wouldn't they need to change the Number of Widgets and Number of Gadgets fields to Sum rather than Group By?

3. ## Re: Count Different Items In One Querry (Access 2000)

Woops, yes of course <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>. I was concentrating too much on getting the dates right, and forgot about the sums. Thank you! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

4. ## Re: Count Different Items In One Querry (Access 2000)

Create a query based on the table.
Turn the query into a Totals query (View | Totals or click the Totals button on the toolbar.)
Leave the Totals option as Group By for the Name, Number of Widgets and Number of Gadgets fields, but change it to Where for the Date field.
Added: Number of Widgets and Number of Gadgets should be set to Sum - thanks to Alexya1 for pointing this out!
Enter the following in the Criteria line in the Date column:

Between Date()+1-Weekday(Date(),2) And Date()+7-Weekday(Date(),2)

This will count the numbers per person over the week starting on Monday containing today.

5. ## Re: Count Different Items In One Querry (Access 2000)

Hans and Trudi,

Thanks to you both. My apology for not putting the questions right. Let me put it differently. What I wanted is actually some type of "count unique". Say a group of people can choose one out of ten different kinds of fruits for breakfast, one out of ten different sandwiches for lunch and one of ten different steaks for dinnerl. At the end of the month, I want to know for each of them how many kinds of breakfast, lunch and dinner each has (regardless of how many times he has it). So I have a table of five fields: date, name, breakfast, lunch, dinner. Can I do a unique count for each person for breakfast, lunch and dinner in one query ?

6. ## Re: Count Different Items In One Querry (Access 2000)

You can probably do this by creating extremely convoluted nested queries, but it is easier to create a custom domain function in a standard module:

Function DCountDistinct _
(ByVal Expr As String, _
ByVal Domain As String, _
Optional ByVal Criteria As String) As Long

Dim strSQL As String

On Error GoTo ExitHandler

strSQL = "SELECT DISTINCT " & Expr & " FROM " & Domain
If Len(Criteria) > 0 Then strSQL = strSQL & " WHERE " & Criteria
DCountDistinct = rst.RecordCount

ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Function
End Function

Use this function in a Totals query:

SELECT LastName, LastName, DCountDistinct("Breakfast","tblMeals","PersonID=" & [PersonID]) AS CountOfBreakfast, DCountDistinct("Lunch","tblMeals","PersonID=" & [PersonID]) AS CountOfLunch, DCountDistinct("Dinner","tblMeals","PersonID=" & [PersonID]) AS CountOfDinner
FROM tblMeals
GROUP BY PersonID;

For large numbers of records, this will probably be slow.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•