I have a table of daily sales revenue for the past 3 years. I need to do a query that will calculate weekly sales revenue for each year and compare it to the same week in the prior years. Anyone have any ideas on this?

Thanks,
Brian

I would suggest breaking the table into three tables- one for each year.

Then run a query to sum a range between 1/1 and 1/7. Then 1/1+7, 1/7+7.. Probably do this with vba. Take those results, put them in a table or array, and do the same for each year. Compare results with vba as the final part of a query form.


Just my two cents- lots of nice in theory things here, but depending on your usage you'll probably be banging your head to do that.

Keeping in mind that there are 52 weeks per year, just do sums for each 7 day period, give it a week number. Then compare against week X, week x+52, week x+2*52, etc.. That would work in a single table, but you still gotta store results for a bit.

BTW- Welcome to techspot!

Select the sales figure for the year and subtract the previous year's figure using a subselect where year = year - 1. Group by week. Something like that. Be more specific and post the table/column names if you want more useful help.

I too was thinking along similar lines of Goalie, but I'd just skip the last table, and do the math in the query.

Takes up a bit less space that way

Dunno why you'd need VBA though. I'd think that'd just make it harder, as all you've got to do with the regular commands is to mulitply the rows (or columns) with 7..

Here's a little Excel style sample (dunno if it's the same in Access, as it's over 4 years since I used it last. And it's only 3 since I used excel). (you'll either need to get at the index number, or have a column for week/index which can be accessed)
At least that's how I think it'd be, since the number in the A column would increase with one, as you drag it downwards (still thinking excel, but it shouldn't be too hard to convert), thus giving you the correct address in the B column on which to add together. You'd still need to add in the code for what table to get the data from though...


BTW: Sorry if the example is wrong, but I'm tired, had a bit to drink and haven't used excel nor access in several years. But the logic behind it should still be applicable.