Data re-creation

[leobear]

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.

[June7]

Why is data 'reset'? What happens to the year's worth of data entry?

[leobear]

Hi June,

Yes because the data is YTD (year to date) data, and I want to recreate it to month to date.

[June7]

You need to calculate difference of sequential records. Check out
http://support.microsoft.com/default...b;en-us;210504
http://allenbrowne.com/subquery-01.html#AnotherRecord