Help with a query

[SDowse]

Hi,

I am new to Access and trying to learn as I go along...so forgive me if the below is basic.

I have a table that tracks visits to a website on a daily basis. The information is (currently) added in manually but I am looking to create a query that has a header for each month with a sum of visits against each header...i.e. a Jan column with a sum of the visits in Jan, a Feb column with a sum of the visits in Feb etc...

I currently have a query per month and a consolidating query - but I am hoping this can all be achieved through a single query?

Thanks in advance,
Me

 

[Joe4]

This task can be accomplished without too much trouble, if your data is structured properly.
Basically, in your data source (table), you do NOT want separate columns for each month (otherwise you are having to add new columns every month, and a well designed table should seldom have to be altered!).

Basically, your data should be structured something like this:

ID      Month     Value
1        Jan         10
1        Feb          8
1        Mar          6
2        Jan         14
2        Feb         16
2        Mar          7
3        Jan          5
...

So each month, you are simply adding new records to your table (and not having to create any new columns)

Then, to get the results for any particular month, you simply query on the Month field.
You could even make your query a Parameter query, which allows you to enter the criteria at run-time when you try to open it.

If you want to see multiple months at one time across multiple columns, you would then just need to create a Crosstab query off of your table.

See this link for more info on Parameter and Crosstab queries: https://support.office.com/en-gb/ar...-a-query-c2806d3d-d500-45a8-8507-ec6af351b6ed

 

[SDowse]

Perfect! Many thanks for your help

 

[Joe4]

You are welcome!