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

[Emile]

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

[NoellaG]

Hi,

you can use the function dateadd(interval,number,date)
like: fldPreviousWeek : datedadd("ww",-1, date())

[ssanfu]

If I understand correctly, maybe a union query will work.
The first query would return the current data, the second query would return last week data and the third query would return last years data for the current week.

You can't use the query designer for the union query, you have to type it in.