Reconstruct table

[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.

 

[dnlbrky]

Hi leobear. See if the following SQL works for you:

SELECT prev.Customer, prev.Month, [prev].[Sales]-Nz([Sales].[Sales],0) AS SalesMTD, prev.Sales AS SalesYTD
FROM
(SELECT Sales.*, [Sales].[Month]-1 AS MonthPrev
FROM Sales) AS prev
LEFT JOIN Sales ON (prev.MonthPrev = Sales.Month) AND (prev.Customer = Sales.Customer);

It takes the original table (which I called "Sales"), and adds a column that subtracts 1 from the current month (to get the previous month). Then it joins this table (subquery, really) to the original table, but the previous month is joined to the current month. The "SalesMTD" (MTD = month to date) is obtained by subtracting the previous sales value from the current value. If the previous value is null (e.g. when the previous month is 190000, which doesn't exist), then it subtracts 0 (i.e. just gives you the value for the current month).

 

[leobear]

Hi dnlbrky, thanks for your prompt reply.

Because the actual dataset is much greater than the one I post here, imagine more customers and more YTD figures, can I recreate a table that regenerate the MTD figure for all customer and all months at once. Or I can only generate based on a month only?

 

[dnlbrky]

Save the SQL as a query, and then just run it (once) whenever the source data (Sales) has updated and you want to see updated results for all months. You don't have to run it multiple times if there are multiple months.

Note that this SQL assumes there is only one row in Sales for a given customer and month. If you were to add a field, like a part number, then the SQL would have to be modified.

 

[leobear]

Thanks again. Since each customer have a lot of month of data but just one sale, may you sheer light on how I may modify the SQL? Tks