Creating Aggregated Time Periods in a query to use with Excel pivot tables

[Tibbs]

I'm a very new Access user, I have a copy of Access 2003 which I'm using to aggregate sales data which I'm then pulling down into pivot tables on excel. I do it this way as I'm the only one in my business with access and there are multiple people who need to use the data in their own analysis.

I have set up my data in tables and linked them and I made a query that I then use as the base for the pivot tables I pull.

I would like to be able to have aggregated relative time periods in the query, so people can choose, for example, latest 4 weeks, latest 12 weeks and Year to date, together with Year ago and 2 years ago.

I already have things like week numbers and period numbers in a table, and I've been manually updating some of the aggregated time periods, but I'm thinking there must be a way to do the updating automatically. I would like to add to this table headers that say 12 weeks, 4 weeks, Year to Date etc., an in the values have 12 weeks TY, 12 weeks YA, 12 Weeks 2YA, if that makes sense?

I must confess that my VBA is terribly basic - just recording and editing and no experience of SQL.

Does anyone have a link to a guide as to how to do this? I'm keen to learn, but my searching hasn't shown up anything.



Thank you in advance!

[ItsMe]

...I would like to be able to have aggregated relative time periods in the query, so people can choose, for example, latest 4 weeks, latest 12 weeks and Year to date, together with Year ago and 2 years ago...

In Access, these types of things are usually accomplished via queries and then they are displayed in Reports or Forms for the End Users. You can certainly export to Excel, but I do not believe exporting to a Pivot Chart is a basic task. There have been changes in Access with respect to Pivot Charts. With the newer versions, Pivot Tables are deprecated. You might try creating a Pivot Table in Access 2003 and then use the Wizard to Export it.

If you are looking for resources to learn, you might try a book like Access Step by Step from the Microsoft Press Store. However, I am not sure you will be able to find the one you need for Access 2003. Maybe Amazon?

Also, newer books on Access tend to focus on Access as a Web Application that depends on SharePoint vs. the traditional Access Desktop application.

Not really an answer to your question but I believe this is information you should consider.

[Tibbs]

Thank you, I've ordered the book from amazon - a nearly new version was less than £5!

[ItsMe]

OK, glad to hear. And welcome to the forum.