Breaking down a weekly rolling total in a sales table

[Tim Zych]

I have a sales table that has rolling totals by week (7 days).

Day 1 sales = day 1 total. Day 2 sales = day 1 + 2, etc. I can figure out a
given day's total by subtracting the prior day total from the current day,
except for day 1 which is always equal to itself.

I need a select query that can breakdown the totals by their daily totals,
not running totals. The approach I was taking was to join tblSales

Here's what I have.

tblSales
Market
SalesDate
Sales

Market and SalesDate is the PK. SalesDate is a date.

tblCalendar
FiscalDay
DayOfFiscalWeek

FiscalDay is a date and is the PK DayOfFiscalWeek has numbers from 1 to 7.

I've tried the following:

SELECT Sales1.Market, Sales1.SalesDate, Sales1.Sales, Sales2.SalesDate,
Sales2.Sales, tblCalendar.DayOfFiscalWeek
FROM
tblCalendar
INNER JOIN
(tblSales AS Sales1
INNER JOIN
tblSales AS Sales2
ON
Sales2.SalesDate=DateAdd("d",1,Sales1.SalesDate) AND Sales1.Market =
Sales2.Market)
ON
tblCalendar.FiscalDay = Sales1.SalesDate
ORDER BY
Sales1.Market, Sales1.SalesDate;

to get to a point where I later can use the DayOfFiscalWeek to subtract the
daily values except for day 1, but this doesn't work. One of the days is
skipped.

Thanks for some help.

 

[Jeff Boyce]

Tim

Have you considered using a Crosstab Query? You could limit the range to
one week, then run a crosstab on it.

Regards

Jeff Boyce
<Access MVP>

 

[Chaim]

It doesn't look like tblCalendar is necessary. You can use the Weekday()
function to determine which day of the week a particular date falls on.

I suspect a design flaw here. The 'rolling weekly sales' is a derivable
quantity based on the daily sales. It is a bad idea in general to store
derivable data in your tables. Store the raw data and derive what you need
from it.

Calculating the running sum: see
http://www.mcse.ms/message188218.html
http://support.microsoft.com/?kbid=210338
http://www.access-programmers.co.uk/forums/showthread.php?t=63646

Google has a lot of hits on 'running sum' or 'cumulative sum'. It is easier
generating the rolling sum than decomposing the rolling sum.

But this is what you've got. So what to do?

Try:

select S.Market, S.Sales, S.SalesDate,
(S.Sales - (select S2.Sales from tblSales as S2
where S2.SalesDate = S.SalesDate - 1) as "Daily Total",
WeekDay (S.SalesDate) as "Day Of Week"
from tblSales S
order by S.SalesDate;

You could also use WeekDayName() to get the name of the day of the week
rather than the number.

Good Luck!

 

[Tim Zych]

I can't I need a couple years worth of data.

BTW I had no choice in the rolling total thing. It's just the way the system
works.

 

[Tim Zych]

thanks after fiddling around I was able to get it to work.

I suspect a design flaw here. The 'rolling weekly sales' is a derivable
quantity based on the daily sales. It is a bad idea in general to store
derivable data in your tables. Store the raw data and derive what you need
from it.

Yeah I know. Not my choice. It's the way the data comes in from another
system.

Not a perfect world, just the real world.

 

[Dale Fye]

Tim

What did your final solution look like?

thanks after fiddling around I was able to get it to work.


Yeah I know. Not my choice. It's the way the data comes in from another
system.

Not a perfect world, just the real world.

 

[Tim Zych]

SELECT S.Market, S.SalesDate, S.Sales,
IIf(Weekday(.[SalesDate])=1,[Sales],.[Sales]-(select S2.Sales FROM
tblSales as S2
WHERE S2.SalesDate = DateAdd("d",-1,S.SalesDate) AND S.Market = S2.Market))
AS DailyTotal
FROM tblSales AS S
ORDER BY S.Market, S.SalesDate;