Results 1 to 6 of 6
  1. #1
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107

    Count the number of different entries in an aggregate query

    I'm creating an aggregate query from my order line table with the following fields:
    Year Entered Month Entered Order Number Who Entered

    I want to get monthly totals of the following:


    number of orders entered that month
    number of order lines entered that month
    how many different people entered orders that month

    I am grouping by Year Entered and Month Entered. Since I am using the order line table, I can get the number of order lines entered that month simply by doing a Count of the Order Number column. To get the other two counts, I need to find out how many different entries are in the Order Number column and Who Entered column.

    I am sure there is a simple solution or function, by I am not coming up with it.

    Thanks!

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show readers your tables and relationships. (jpg from screen capture is OK)
    or a copy of the database with enough data to highlight the issue. You should remove anything private/confidential.

  3. #3
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    Below is a screen dump of the columns in my table:

    Click image for larger version. 

Name:	orders.jpg 
Views:	10 
Size:	187.9 KB 
ID:	24550


    I sort by columns 1 and 2, I want a full count of column 3, but I want distinct counts of columns 4 and 5.

  4. #4
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    I meant to say I group by columns 1 and two, not sort.

  5. #5
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    you can use DCount to represent this on a form. Or you could use Select Distinct Count("OrderNumber") from tblOrders.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I would recommend against using domain functions (dcount, dsum, etc) in queries, they have a tendency to make things really slow.

    Column 4 and 5 appear to be identical so you only need to use one of them. Assume your table is named tblTest

    Just so it's easier to see break this down into 3 queries:

    qryOrderCount
    Code:
    SELECT tblTest.YearEntered, tblTest.MonthEntered, Count(tblTest.OrdersEntered) AS TotalOrdersEntered
    FROM tblTest
    GROUP BY tblTest.YearEntered, tblTest.MonthEntered;

    qryUniqueOrders
    Code:
    SELECT [UniqueOrdersPrequery].YearEntered, [UniqueOrdersPrequery].MonthEntered, Count([UniqueOrdersPrequery].TotalOrdersEntered) AS UniqueOrdersFROM (SELECT tblTest.YearEntered, tblTest.MonthEntered, tblTest.OrdersEntered AS TotalOrdersEntered FROM tblTest GROUP BY tblTest.YearEntered, tblTest.MonthEntered, tblTest.OrdersEntered)  AS [UniqueOrdersPrequery]
    GROUP BY [UniqueOrdersPrequery].YearEntered, [UniqueOrdersPrequery].MonthEntered;
    qryUniqueUsers
    Code:
    SELECT YearEntered, MonthEntered, Count(Users) AS CountOfUsers
    FROM (SELECT tblTest.YearEntered, tblTest.MonthEntered, tblTest.users AS Users FROM tblTest GROUP BY tblTest.YearEntered, tblTest.MonthEntered, tblTest.users)  AS UniqueUsersPrequery
    GROUP BY YearEntered, MonthEntered;
    each query has a separate grouping, then it's just a matter of combining them into a single query.

    You can test it against your domain functions but think you'll find the query driven solution quicker.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 08-16-2012, 09:04 AM
  2. Replies: 8
    Last Post: 03-14-2012, 05:40 PM
  3. Replies: 6
    Last Post: 07-25-2011, 01:54 PM
  4. Replies: 0
    Last Post: 04-25-2011, 07:58 PM
  5. Query to group and count field entries
    By newtoAccess in forum Queries
    Replies: 4
    Last Post: 11-29-2010, 09:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums