Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Create a query based on the table.
    Add the four fields.
    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. #5
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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 rst As New ADODB.Recordset
    Dim strSQL As String

    On Error GoTo ExitHandler

    strSQL = "SELECT DISTINCT " & Expr & " FROM " & Domain
    If Len(Criteria) > 0 Then strSQL = strSQL & " WHERE " & Criteria
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic
    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
  •