Showing 8 weeks of weekly units

[tbonehwd]

I have a table called data in it we import the weekly units sold at our customers pos stations. The fields in the table are CMT_CODE(Customer Code), WEEK(mmddyyyy this date is always a sunday), ISBN(Item Number), UNITS_SOLD, INV_ON_HAND, INV_ON_ORDER. I want to enter a date for example 12/04/2005 and a specific CMT_CODE and have the query give me the following columns ISBN, CURRENT_WEEK_UNITS, WEEK_2_UNITS, WEEK_3_UNITS, etc... for eight weeks and the CURRENT_WEEK_INV_ON_HAND, CURRENT_WEEK_INV_ON_ORDER. If anyone has any suggestions please let me know.


Thanks,

Tbonehwd

 

[tbonehwd]

In case what I wrote earlier doesn't make much since this maybe this will help I have a table that we store weekly sales from our customers called data I want to write a query that will pull the current weeks sales, week_2 sales, week_3 sales etc...

something like

sqlstr="SELECT (DATA.CYP_UNITS_SOLD) As Current_Week, ISBN " & _            
"FROM DATA " & _            
"WHERE DATA.WEEK=#12/04/2005# AND DATA.CMT_CODE='AMZ' " & _            
"GROUP BY DATA.CYP_UNITS_SOLD, DATA.ISBN;"

How can I do the above but have another field called Week_2 where DATA.WEEK=#12/04/2005# -1 week