Multiple count query

Question

I am hoping someone can help me with a sql query that I am looking to build.  To best describe my inquiry, consider the following Order table:

order id	required by date	close date	order date	crew date	…
1	8/9/2020	8/10/2020	…	…	…
2	8/9/2020	8/10/2020	…	…	…
3	8/11/2020	8/13/2020	…	…	…
4	8/11/2020	8/13/2020	…	…	…
5	8/13/2020	8/13/2020	…	…	…

I am looking for a query that I will incorporate into an access form to show counts for upcoming dates for my orders.  This will help my office team be able to schedule the work for the internal team managing orders.  More specifically, given the snippet above, I would like to show something like:

scheduling outlook
Date	required	closing
8/9/2020	2	0
8/10/2020	0	2
8/11/2020	2	0
8/12/2020	0	0
8/13/2020	1	3

I have left out the other dates out from the snippet above for simplicity.  I know that I could easily do a 

select order.[required by date], count(*) from order where (...) group by order.[required by date]

to get the first column.  However, is there a way a can write the query to give me the next 10 days counts for each of my desired date columns (required by, closing, order date, crew date, etc)?

Answer 1

However, is there a way a can write the query to give me the next 10 days counts for each of my desired date columns (required by, closing, order date, crew date, etc)?

Hi jchavez_chi,

As an alternative in making complicated queries, you could - and this is on the very moment my favourite way of working - make an unbound form with the next 10 days in one column, and the accompanying colums for "required" and"closing".

In the Open event of this form the columns ''required" and "closïng" can be filled depending on the date.

(Dbl)Clicking on a "date" field could open a form with all the details concerning that day. (Dbl)Clicking on the "required" or "closing" field could open a form with the corresponding order records.

Imb.

Answer 2

For me the easiest to understand and maintain is a correlated subquery, something like:

SELECT Q.dt ,
    (SELECT COUNT(O.OrderID)
    FROM ORDER AS O
    WHERE O.[Required by]= O.dt)
    AS ReqCnt,
    (SELECT COUNT(O.OrderID)
    FROM ORDER AS O
    WHERE O.[Close date]= Q.dt)
    AS CloseCnt,
    (SELECT COUNT(O.OrderID)
    FROM ORDER AS O
    WHERE O.[Order date]= Q.dt)
    AS OrderCnt,
    (SELECT COUNT(O.OrderID)
    FROM ORDER AS O
    WHERE O.[Crew date]= Q.dt)
    AS CrewCnt
FROM Q
ORDER BY Q.dt ASC

where Q is a query or temp table that returns the 10 sequential dates (dt) you need counts for.  I would probably opt for building a temp table with a single column in it (dt) in advance of running this query that has the 10 sequential dates that I want to report on.  But it may be that you are only concerned with the next 10 order dates, in which case Q could be a pre-built query like:

select top 10 [Order date] as dt from Order where [Order date]>=Date() order by [Order date]

-Bruce

Answer 3

lmb,

thank you.  I will have to try the unbound form with the associated columns (10 dates, required, close, crew, etc) and leverage the open event to query and fill the counts.  Had not thought of that and believe this is something that can come in handy for some other forms I am building.  I do like and incorporate the single and double clicks to pull the details throughout my forms....love it!

~jchavez_chi

Answer 4

Subqueries!!!  That is exactly the query I was looking for.  Thank you!  I figured there was a way to leverage queries within queries.  I was really struggling with the syntax.  Your direction hit the nail on the head.  Thanks again.

~jchavez_chi