QUERY: Quarterly Subtotals on Multi-Year Report

  • Thread starter Robert Blair via AccessMonster.com
  • Start date
R

Robert Blair via AccessMonster.com

Using Access 2000. The data for the report is in one table - tblTimeCards.
The critical fields are [ProjectID], [WorkDate], [Description] and
[BillableHours]. For audit purposes, I need to create a report for each
[ProjectID] which will display the [WorkDate], [Description] and
[BillableHours] in chronological order, with sub-totals for billable hours
by quarter and total billable hours for the entire project. The projects
can span several years.

I'm having difficulty getting the subtotals by quarter. So far, I've
managed to create the query to pull in all of the timecards for each
project, sort them by date and generate the report, broken down by
quarters, and print "Totals Billable Hours for Quarter", but the subtotal
printed on the report is only for the first timecard of the quarter, not
the total of all hours in the quarter.

Any help is appreciated.
 
R

Robert Blair via AccessMonster.com

As a follow-up, I've created a second query which generates only the result
Q1, Q2, Q3 and Q4 for each year and correctly reports the subtotals for
each quarter of each year, sorted chronologically.

The report sorts the time cards by date worked and groups them by quarter,
printing a footer at then end of each quarter.

Am I better off creating a sub-report in the footer, based upon the results
of the second query, to just print that data or is there a way to combine
the results of the "quarterly totals" query into the first "detailed time"
query?
 
M

MGFoster

Robert said:
As a follow-up, I've created a second query which generates only the result
Q1, Q2, Q3 and Q4 for each year and correctly reports the subtotals for
each quarter of each year, sorted chronologically.

The report sorts the time cards by date worked and groups them by quarter,
printing a footer at then end of each quarter.

Am I better off creating a sub-report in the footer, based upon the results
of the second query, to just print that data or is there a way to combine
the results of the "quarterly totals" query into the first "detailed time"
query?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

In the Qtr footer do you have a TextBox whose ControlSource is this:

=Sum(BillableHours)

if so, it should be summing all the billable hours in the preceeding
Detail section.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnKTVIechKqOuFEgEQJZ2ACg1txT98yUHltBp+CKtmDa9REkdAIAoNLj
VpPs//6uoQO3OOEotQGUftWV
=zzLS
-----END PGP SIGNATURE-----
 
R

Robert Blair via AccessMonster.com

That was way too easy. I overlooked the simple answer trying to find a
complicated one. Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top