Grouping Sales Numbers

[jturn00]

I am trying to take some data and group it according to # of months from a date. And put all the results into a table. I have an order date. Say 1/1/2000. I order 10 units for ordernumber 1. This information is stored in one table. Another table has the order transactions and the purchase date of the customer say 5/15/2000. (Each row in this table stores a transaction).

I want to combine the data from these two tables into one table.

I know that I can use datediff to find the difference in months from the order date to the Purchase date of the customer.
The new table would have columns from 0 to 36 where this is the number of months from the1/1/2000 date to the order date of the customer (5/15/2000). When the appropriate row (identified by order number) is found, then the number of units that a customer ordered would be placed in the appropriate column. So an order on 5/15/2000 would result in a datediff of 5 and if the customer bought 3 units that would be placed into the row/column of the table.

So each row in the new table would have the order #, the total number ordered and then colums which recorde the amount customers bought a # of months from the orignal order date.

I hope this makes sense. Let me know and I can clarify.

Jeff

 

[Norie]

Jeff

Do you really need to create a new table?

Couldn't you just use a query for this?

 

[jturn00]

It could be a query. The most important aspect is determining how to place the number of customer purchase orders in the correct column based off the datediff. I worked on it yesterday and cannot seem to figure out how to tell access that if the datediff result is 5 then put the customer purchase orders (a field) into the 5 column. The design is open right now. Any suggestions are welcome as I am developing the application as we speak.

 

[Andrew Fergus]

You should be able to do this with a crosstab query (click Query -> Crosstab Qiery), add your crosstab 'row headings' (usually something like customer, branch or region or a number of these perhaps?) and leave the 'group by' as is for those, add the item you wish to sum (value or quantity?), set the group by for that to 'sum' and make it the crosstab 'value' and for the crosstab 'column heading' use something like :
Period_Number: DateDiff("m",[Your_date_field],Date()) and lave the group by as is. Have a play around with the crosstab queries and see if they return what you want.
HTH, Andrew