cgibbons123
asked on
By Week report access
Hello
I need to create a query/Report to display a count of all units sold for each model during every week of any given year.
So it will look like
From To Week Model1 Model2 Total
1/1/05 8/1/05 1 3 4 7
9/1/05 15/1/05 2 2 3 5
....
9/1/05 31/12/05 52 6 3 9
So to get the total for the whole year would be somthing like
SELECT Count(table.ID) AS Count
FROM table
WHERE (((table.date) Between #1/1/2005# And #12/31/2005#));
but how do I manage to do it for each week also without having to do like 52 queries.?
I need to create a query/Report to display a count of all units sold for each model during every week of any given year.
So it will look like
From To Week Model1 Model2 Total
1/1/05 8/1/05 1 3 4 7
9/1/05 15/1/05 2 2 3 5
....
9/1/05 31/12/05 52 6 3 9
So to get the total for the whole year would be somthing like
SELECT Count(table.ID) AS Count
FROM table
WHERE (((table.date) Between #1/1/2005# And #12/31/2005#));
but how do I manage to do it for each week also without having to do like 52 queries.?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
e.g.
WeekOfYear: Week(table.date)
Convert to Totals query
use the following agregate functions to get what you want
GroupBy to group by Week
Min for From
Max for To
Sum for rest
Steve