To reconstruct a data table

[wilkins]

Hi all, this is my first post and I want to reconstruct one table in
access but not sure how to do it,

My current Table look like
Customer Sales Month
A 1 190001
B 1 190001
A 2 190002
B 2 190002

Because the sales data is accumulated (reset every January), I want to
recreate with monthly sales.

Customer Sales Month
A 1 190001
B 1 190001
A 1 190002
B 1 190002

Therefore the step should be
1) For every year Jan (xxxx01), the sales will be same
2) For other months, the sales will be deducted with the previous
month sales to get the month sales figure.

But I am not sure how to use SQL to create such table, and the table
need to rerun everytime I have new data coming in.

Can anyone teaches me how to do that?

Thanks.

 

[Bob Barrows]

Hi all, this is my first post and I want to reconstruct one table in
access but not sure how to do it,

My current Table look like
Customer Sales Month
A 1 190001
B 1 190001
A 2 190002
B 2 190002

Because the sales data is accumulated (reset every January), I want to
recreate with monthly sales.

Customer Sales Month
A 1 190001
B 1 190001
A 1 190002
B 1 190002

Therefore the step should be
1) For every year Jan (xxxx01), the sales will be same
2) For other months, the sales will be deducted with the previous
month sales to get the month sales figure.

But I am not sure how to use SQL to create such table, and the table
need to rerun everytime I have new data coming in.

Can anyone teaches me how to do that?

I could, but i would be doing you a disservice. Calculated data should not
be stored except in very specific situations, such as:
1. historical calculations that depend on factors that change (such as tax
or interest rates) and you don't store the historic values of those factors.
2. the calculation requires an exorbitant amount of time to complete

This does not appear to be one of those situations. Your plan should be to
create a query called, perhaps, "AdjustedMonthlySales", that simply returns
the data you want, calculating it every time the query runs.

Let me confirm that I understand your requirement. If you add month 3 data:
A 4 190003
B 5 190003

The result should be this:
A 2 190003
B 3 190003

Correct?
If so, you need a correlated subquery to do the calculation. Like this (I am
assuming that Month field is a Number datatype - if Text, you will need to
convert it to a number in the subquery before doing the subtraction):

Select Customer,
, Sales - Nz(
(Select Sales FROM SalesByMonth As q WHERE s.Customer=q.Customer AND
q.[Month] = s.[Month] - 1)
,0) As AdjustedSales
, [Month]
FROM SalesByMonth As s

Incidently, "Month" is the name of a VBA function, making it a reserved
keyword that should never be used as an object (table, field, etc.) name.
"SalesMonth" would be a more descriptive name and would avoid the reserved
keyword violation.