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

    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
    Location
    Kentucky
    Posts
    8,925
    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
    Location
    Belgium
    Posts
    780
    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