Grouping & Counting Records by Date Ranges

[PATRICKGILL]

I need to generate an Access 2003 query that will produce a data summary of item sales history in monthly ranges that I can adjust.

Here is an simplified example data table I want to summarize:

ItemCode OrderDate Quantity
ABC-0001 5/5/2006 11:59:00 AM 3
ABC-0001 5/8/2006 9:42:00 AM 2
ABC-0002 5/10/2006 12:02:00 AM 5
PEN-0010 6/5/2006 11:59:00 AM 1
CAC-0001 7/2/2006 11:59:00 AM 3
ABC-0001 7/20/2006 11:59:00 AM 8
ABC-0001 7/5/2006 11:59:00 AM 1
ACT-0201 5/8/2006 11:59:00 AM 4
ACT-0201 5/5/2006 11:59:00 AM 5

Here is what I am looking to create in a query:

The first column (column A) should contain the product reference(ItemCode) - I can do this.

The second column(column B) should contain the beginning date of the period - I need help here.

The third column (column C) should contain a sum of the number of units sold during the period - I need help here to make it match the sum of the quantity for the desired period.

ItemCode OrderDatePeriod AmountSold

ABC-0001 5/1/2006 5
ABC-0001 6/1/2006 0
ABC-0001 7/1/2006 9
ABC-0002 5/1/2006 5
ABC-0002 6/1/2006 0
ABC-0002 7/1/2006 0
PEN-0010 5/1/2006 0
PEN-0010 6/1/2006 1
PEN-0010 7/1/2006 0
CAC-0001 5/1/2006 0
CAC-0001 6/1/2006 0
CAC-0001 7/1/2006 3
ACT-0201 5/1/2006 9
ACT-0201 6/1/2006 0
ACT-0201 7/1/2006 0

As you can see above I need the same date ranges for each item, if sales are 0 for that range I need to return a 0. Any item history in the range needs to summarize into the starting date period and the quantity sold as the sum for the AmountSold field. I would like to format the date to only show 6/1/2008, 7/1/2008, etc. for the range.

I am a novice Access user and really need step by step instructions on what to do. I am not sure if a function can do this or I need a date range table with a function. Thank you in advance for your help. Please let me know if you have any questions.

 

[Remou]

It is easy enough to format the date in a number of ways:

Select ItemCode, Format(OrderDatePeriod, "mm/dd/yyyy") AS FDate, Sum(AmountSold) FROM tblT GROUP BY ItemCode, Format(OrderDatePeriod, "mm/dd/yyyy")

However, you will need a calendar table of some description with all the dates if you want to show zero for dates with no sales.

 

[PWise]

remou:

should it not be

Select ItemCode, month(calendarmonth ) SalesMonth,year(calendaryear) SalesYear, Sum(AmountSold) 
FROM calendar 
left join SalestblT 
on month(calendarmonth )=month(OrderDatePeriod     )
 and Year(calendarmonth )=Year(OrderDatePeriod     )
GROUP BY ITemCode, month(calendarmonth ), year(calendarmonth)

 

[Remou]

pwise

Perhaps, but the OP seems to require data by day, not month and there is no mention of a calendar table by the OP. I think I would be inclined to join of the date or the formatted date and then group rather than join on date parts.

 

[PATRICKGILL]

The sales data is daily, but I will be summarizing by month and maybe by week. I think I will have to create a date range calandar table to allow for the zeros for certain periods if no sales have occured.

 

[PWise]

note this
ItemCode OrderDate Quantity
ABC-0001 5/5/2006 11:59:00 AM 3
ABC-0001 5/8/2006 9:42:00 AM 2

ABC-0001 5/1/2006 5


ItemCode OrderDate Quantity
ABC-0001 7/20/2006 11:59:00 AM 8
ABC-0001 7/5/2006 11:59:00 AM 1

ItemCode OrderDatePeriod AmountSold
ABC-0001 7/1/2006 9

 

[Remou]

pwise

Those American date formats ...