Max number of orders over a variable time period

J

Jay

I am trying to build a query that pulls the maximum number of orders
over a period of 3 days. Could anyone help me out?
 
K

KARL DEWEY

SELECT TOP 1 Format(Left(Partition([Date open],0,999999,3),7),"mm/dd/yyyy") &
" : " & Format(Right(Partition([Date open],0,999999,3),5),"mm/dd/yyyy") AS
[Date Group], Sum([Change Requests].x) AS SumOfx
FROM [Change Requests]
GROUP BY Format(Left(Partition([Date open],0,999999,3),7),"mm/dd/yyyy") & "
: " & Format(Right(Partition([Date open],0,999999,5),5),"mm/dd/yyyy");

Change [Change Requests] to your table.
Change [Date open] to your date field.
Change [x] to your quanity field.
Change the 3 in Partition([Date open],0,999999,3) if you want other than a
three-day span.

You can also add additional criteria such as Between [Enter start date] And
[Enter ending date] to narrow the search.
 
K

KARL DEWEY

In the last line of the SQL I did not edit the 5 to your 3 like --
0,999999,5 to be 0,999999,3

KARL DEWEY said:
SELECT TOP 1 Format(Left(Partition([Date open],0,999999,3),7),"mm/dd/yyyy") &
" : " & Format(Right(Partition([Date open],0,999999,3),5),"mm/dd/yyyy") AS
[Date Group], Sum([Change Requests].x) AS SumOfx
FROM [Change Requests]
GROUP BY Format(Left(Partition([Date open],0,999999,3),7),"mm/dd/yyyy") & "
: " & Format(Right(Partition([Date open],0,999999,5),5),"mm/dd/yyyy");

Change [Change Requests] to your table.
Change [Date open] to your date field.
Change [x] to your quanity field.
Change the 3 in Partition([Date open],0,999999,3) if you want other than a
three-day span.

You can also add additional criteria such as Between [Enter start date] And
[Enter ending date] to narrow the search.

Jay said:
I am trying to build a query that pulls the maximum number of orders
over a period of 3 days. Could anyone help me out?
Click to expand...
Click to expand...
 
Top