Results 1 to 3 of 3
  1. #1
    Hammer is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011

    Help with Distinct Count in Query and date grouping

    Good afternoon

    I am seeking assistance with a query that uses "distinct count" to count individual sales orders numbers per day. I have a query set with 3 columns - Load date _ Order No _ Site No. This query runs on a "Between [start date] and [end date] range in the load date column and pulls in all the sales order numbers, many of which are duplicated.
    I then have a second query which counts the distinct sales order numbers in the first query for a single day, all it displays is the number of distinct order numbers, I know nothing about SQL but I managed to copy some bits off of forums to get this to run, it is set as below
    SELECT Count(*) AS Expr1
    FROM (SELECT DISTINCT T1.[Order No] FROM [Daily Order Count] AS T1) AS T1

    I would like the query to show the distinct count of sales order numbers and also display the dates in a range so I could view a weeks at a time by and also display the sites ,however as this is well out of my knowledge range I would not know where to start so I am hoping somebody can assist.

    Many thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    make Q1 to do the distinct results,
    then make Q2 using Q1 to count

  3. #3
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    If I understand correctly, you have orders (I assume acregates or so) and for each order there can be many loads or delivery orders, in my example I assume they are stated in table Deliveries.
    If you want to count the orders for which there load orders between date1 and date 2, you can write a groups query on orders. Something like (substitute my fieldnames by the fieldnames U use)

    select orderID, year(DeliveryDate) as YearDelivery, datepart("ww",DeliveryDate) as WeekDelivery
    from Deliveries
    where DeliveryDate between #Date1# and #Date2#
    group by orderID, year(DeliveryDate), datepart("ww", DeliveryDate)

    save this query, for example with the name qtotOrdersPerWeek

    Based on this query you can than create a second groupsquery to count the orders/week:

    select count(OrderID) as CountOrders, YearDelivery, WeekDelivery
    from qtotOrdersPerWeek
    group by OrderID

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

Similar Threads

  1. Replies: 0
    Last Post: 02-11-2020, 08:12 PM
  2. Replies: 4
    Last Post: 01-15-2019, 11:50 AM
  3. distinct count with criteria and max date
    By TheShabz in forum Queries
    Replies: 3
    Last Post: 09-23-2011, 06:08 AM
  4. Distinct count in a query
    By Fabdav in forum Access
    Replies: 5
    Last Post: 09-14-2011, 04:53 PM
  5. Count distinct records in parameterized query
    By SilverSN95 in forum Access
    Replies: 5
    Last Post: 07-27-2010, 09:31 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 - Senior Forums