How to get the current week -1 week and CYTD -1 year (use a CYweek in an number format).

[EmileEA]

Dear Access wizards,

Currently I'm using Access to store weekly data of our market.
Every week I receive a flatfile which I integrate in Access.
As a timekey I'm using a CYWEEK (example: 201615).

Now the thing is, when I make a query, I want to be able to see the latest week (highest value). Therefore I used the max function and had to create a separate query. This worked fine.

However I would also like to be able to return the values of the current week and the previous week. Which in this case, would be 201615-1 = 201614. In excel I have data connections to access so I'm able to refresh the file every week and with that update all tables and graphs. Therefore, I don't want have to fill in the criteria of the previous week, manually in access.

This problem is the same as I want to see the current week for last year. (201615-100=201515). Does anyone know a way on how to solve this?

I have attached an excel file which could clarify the problem a little better.

Thanks in advance!!!

Kind regards,
Emile

 

[Joe4]

Cross-posted: How to get the current week -1 week and CYTD -1 year (use a CYweek in an number format).

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).