Choose entry by Week

  • Thread starter Thread starter MEilu
  • Start date Start date
M

MEilu

Hello,

I have a table with the following fields:
ID (Key)
Date (Key)
Qty

ID and Date are the keys.

I was wondering if it's possible to write a query that
would sum of the Qty of each week?

ie: If the table had the following entries
ID Date Qty
A 1/1 1
B 1/1 1
A 1/2 1
B 1/2 1
A 1/7 1
B 1/7 1
A 1/9 1
B 1/9 1
A 1/10 1
B 1/10 1


The Query would return

ID Week Of Qty
A 1/1 3
B 1/1 3
A 1/8 2
B 1/8 2

I know that I could do this with a report. But I would
also like to export the result to excel. and with a
report ... it just doesn't look very nice. I want it to
look like a spreadsheet. And the report doesn't format
it like that.

Thanks for any input :-)
Meilu
 
Meilu

First, a caution. MS Access treats "Date" as a reserved word -- if your
field is so-named, you will confused Access and give yourself headaches.
Consider renaming that field to something else, like "StartDate", or
"SalesDate", or ...

Access has a Week() function that returns a week-of-year number (week of
1/1/xxxx is "1"; week including 12/31/xxxx is "52"). In a query, you could
use Week([SalesDate]) to generate week numbers, then use a Totals query to
group by ID and WeekNumber, Sum(ming) on Amt.

Good luck!

Jeff Boyce
<Access MVP>
 
Back
Top