Quarterly Addition Field in Report

[outofpractice]

This is probably simple, but I have not been using Access/VB for several years now and my skills are quite rusty. So my situation is this:

My area has web reports that are put together monthly and it has always been done using Excel. This task has been handed back to me while a co-worker is on maternity leave - I'm a little dismayed at all the different spreadsheets being managed/stored for these reports so have decided to put all of this information into Access so it is all in one place.

I have the tables set up without any issue and have a query set up for the form to pull from for a year (right now only a year, I'll get the monthly reports set up as well).

So what I am stuck on is:

I have the report bringing back the rows of data, by month in the detail area like I want. Now what I want to do is add in a Quarterly Total count. I have the annual added as a sum for all of the fields, but can't work through how to only have the report look at, for example:

Month equals either 1, 2, or 3 or even (January, February, or March) and if it does then sum the "Site_Visits" field

And I would only want this field to appear once and only if there is a count in it.

Should this maybe be a separate query that a field on the report references?

Hope this makes sense and thanks in advance for any assistance.

 

[Ranman256]

Use a form with 2 text boxes; txtStartDate, txtEndDate.
All report queries will use this as the criteria to pull data.
Enter your Quarterly dates in the form to pull the data.

 

[plog]

Not completely clear on what you want, but it sounds like you want a sub-total on your report for every quarter? Right? Close?

If I'm in the ballpark, then you should set up your query so you can do that on your report. You would create a callculated field in your query to determine the quarter for each record. This could be done via the DatePart function (http://www.techonthenet.com/access/functions/date/datepart.php) if you have a date for each record, if you have just a month, then you would do it by dividing the month by 4 and adding 1.

Then with that you can set up your report to have a Quarter grouping, in that you could add fields to calculate the sub-total for that quarter like you have done for your annual total.

 

[outofpractice]

Not completely clear on what you want, but it sounds like you want a sub-total on your report for every quarter? Right? Close?

If I'm in the ballpark, then you should set up your query so you can do that on your report. You would create a callculated field in your query to determine the quarter for each record. This could be done via the DatePart function (http://www.techonthenet.com/access/functions/date/datepart.php) if you have a date for each record, if you have just a month, then you would do it by dividing the month by 4 and adding 1.

Then with that you can set up your report to have a Quarter grouping, in that you could add fields to calculate the sub-total for that quarter like you have done for your annual total.

Thanks - I'll check out what you have to see what I can come up with.

For this particular report, on the entry form I am just using a month/year selection so every entry will be associated with a combination of a month and a year. Before the report is generated there will be another form for the requested year to be selected (This Year selection is used by the Query the report uses to pull data only on records for that year).

So when the data comes back it current looks like:

Page View Visits Action X
January 23 12 201
February 43 23 107
March 30 28 27
April 98 42 87


What I want to happen is it to come back like this:

Page View Visits Action X
January 23 12 201
February 43 23 107
March 30 28 27
1st Qtr 96 63 335
April 98 42 87

But what I am currently getting is more like this, where the field I am trying to use for Quarter repeats after every record:

Page View Visits Action X
January 23 12 201
1st Qtr x x x
February 43 23 107
1st Qtr x x x
March 30 28 27
1st Qtr x x x
April 98 42 87
1st Qtr x x x

Hope that makes sense and thanks for the suggestion.

 

[outofpractice]

And - the formatting of my examples did not work out at all.

The example was supposed to be 3 columns across the top and then there were the rows which begin with the respective month (No heading above the month)

My IT area is strict on uploads etc or I'd just post an image/pdf of exactly what I currently have.

Tried to force the spaces with - instead:

So when the data comes back it current looks like:

------------Page View------Visits-----Action X
January--------23-----------12---------201
February-------43-----------23---------107
March----------30------------28---------27
April------------98-----------42----------87


What I want to happen is it to come back like this:

------------Page View------Visits------Action X
January-------- 23---------- 12--------- 201
February------ 43----------- 23--------- 107
March----------30----------- 28----------27
1st Qtr--------- 96---------- 63----------335
April----------- 98-----------42---------- 87

But what I am currently getting is more like this, where the field I am trying to use for Quarter repeats after every record:

----------Page View ---Visits ---Action X
January---- 23 ---------12 -------201
1st Qtr ------x---------- x--------- x
February--- 43--------- 23-------- 107
1st Qtr------ x---------- x--------- x
etc

 

[plog]

Sounds like I understood it. You should read up on Groupings in Reports. Try this link:

https://support.office.com/en-us/ar...y-report-6a58e9ab-9f74-4689-83b6-c63cddb2c7f9