Help with a Monthly Summary Report

Hi,

Since this question involves both a query summary and a report, I tried to
cross post to microsoft.public.access.queries.

I am working of a volunteer reporting form for a charity. They have to
submit a monthly summary report to the government as required by their grant
funding requirements. We submit a month in arrears. For example, we submit
January’s activity report at the beginning of March. By the time we run the
report, all of the activity has been recorded.

Below is a sample of the report:

Program | Volunteer Ct | Total Hours | Total Miles | Donation
| Total
Blood Donor
Hospital
Education
Welfare
Funerals
Comm Serv
Grand Total

For simplicity, I have not listed all of the program categories. There are
a total of 19 categories (and 19 lines) on the report. Since this is a
government form, I must use their form without any changes.

I have a project table which has one row per project / charity event. It is
keyed by project number. The Project Table contains:

ProjNo
ProjName
ProgType (Blood, Hospital, Education, Welfare, Funerals, Comm Serv)

I have a project volunteer activity table which has one record per project,
per volunteer, per date. The key to this table is Project Det No. and it is
an automatically assigned number. This table contains the following fields:

ProjDetNo (automatically assigned)
ProjNo (foreign key to Project table)
Date worked
Hours worked
Miles driven
Amount donated

To create this report, I know I have to:
- Left join the Project Volunteer Activity table to the Project table
- Select only those activity records that have a work date for the specified
month.
- Sort the activity records by program and summarize hours worked, miles
driven, and amount donated by program.

Here are my issues:

1. I’m unsure how to get query / SQL to summaries the multiple activity
records into a single row per program.

2. Assuming I can create a summary row, how do I associate the activity
program with a specific line on the report? I know I can setup a foreign key
so that I can assign an activity program to a specific line on the report.
But my what I don’t know how to do is get the Access report to print the
first record on the 5th line, the second record to print on the 8th line, the
third record to print on the 9th line, the fourth record to print on the 11th
line, and then print the totals on the total line.


I would appreciate any help I can get on this issue.


Thanks,



Dennis

Dennis

Have you looked into using a Cross-tab query/report?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Dennis" wrote in message
...
Hi,

Since this question involves both a query summary and a report, I tried to
cross post to microsoft.public.access.queries.

I am working of a volunteer reporting form for a charity. They have to
submit a monthly summary report to the government as required by their
grant
funding requirements. We submit a month in arrears. For example, we
submit
January's activity report at the beginning of March. By the time we run
the
report, all of the activity has been recorded.

Below is a sample of the report:

Program | Volunteer Ct | Total Hours | Total Miles |
Donation
| Total
Blood Donor
Hospital
Education
Welfare
Funerals
Comm Serv
Grand Total

For simplicity, I have not listed all of the program categories. There
are
a total of 19 categories (and 19 lines) on the report. Since this is a
government form, I must use their form without any changes.

I have a project table which has one row per project / charity event. It
is
keyed by project number. The Project Table contains:

ProjNo
ProjName
ProgType (Blood, Hospital, Education, Welfare, Funerals, Comm Serv)

I have a project volunteer activity table which has one record per
project,
per volunteer, per date. The key to this table is Project Det No. and it
is
an automatically assigned number. This table contains the following
fields:

ProjDetNo (automatically assigned)
ProjNo (foreign key to Project table)
Date worked
Hours worked
Miles driven
Amount donated

To create this report, I know I have to:
- Left join the Project Volunteer Activity table to the Project table
- Select only those activity records that have a work date for the
specified
month.
- Sort the activity records by program and summarize hours worked, miles
driven, and amount donated by program.

Here are my issues:

1. I'm unsure how to get query / SQL to summaries the multiple activity
records into a single row per program.

2. Assuming I can create a summary row, how do I associate the activity
program with a specific line on the report? I know I can setup a foreign
key
so that I can assign an activity program to a specific line on the report.
But my what I don't know how to do is get the Access report to print the
first record on the 5th line, the second record to print on the 8th line,
the
third record to print on the 9th line, the fourth record to print on the
11th
line, and then print the totals on the total line.


I would appreciate any help I can get on this issue.


Thanks,



Dennis

Jeff,

No, but I will now. Thanks for the lead.


--
Dennis

You should be able to get most of this with a simple totals query of your
project table and activity table.

Group By Program and sum the Hours Worked, Miles Driven, and Amount Donated.
Set the criteria under the Date Worked to your specific range.

I don't know how you can get a Volunteer Ct since there doesn't seem to be a
volunteer field in any table.

Your report design will have multiple sections that determine the position
on the page. Play with it.
--
Duane Hookom
Microsoft Access MVP


"Dennis" wrote:

Hi,

Since this question involves both a query summary and a report, I tried to
cross post to microsoft.public.access.queries.

I am working of a volunteer reporting form for a charity. They have to
submit a monthly summary report to the government as required by their grant
funding requirements. We submit a month in arrears. For example, we submit
January’s activity report at the beginning of March. By the time we run the
report, all of the activity has been recorded.

Below is a sample of the report:

Program | Volunteer Ct | Total Hours | Total Miles | Donation
| Total
Blood Donor
Hospital
Education
Welfare
Funerals
Comm Serv
Grand Total

For simplicity, I have not listed all of the program categories. There are
a total of 19 categories (and 19 lines) on the report. Since this is a
government form, I must use their form without any changes.

I have a project table which has one row per project / charity event. It is
keyed by project number. The Project Table contains:

ProjNo
ProjName
ProgType (Blood, Hospital, Education, Welfare, Funerals, Comm Serv)

I have a project volunteer activity table which has one record per project,
per volunteer, per date. The key to this table is Project Det No. and it is
an automatically assigned number. This table contains the following fields:

ProjDetNo (automatically assigned)
ProjNo (foreign key to Project table)
Date worked
Hours worked
Miles driven
Amount donated

To create this report, I know I have to:
- Left join the Project Volunteer Activity table to the Project table
- Select only those activity records that have a work date for the specified
month.
- Sort the activity records by program and summarize hours worked, miles
driven, and amount donated by program.

Here are my issues:

1. I’m unsure how to get query / SQL to summaries the multiple activity
records into a single row per program.

2. Assuming I can create a summary row, how do I associate the activity
program with a specific line on the report? I know I can setup a foreign key
so that I can assign an activity program to a specific line on the report.
But my what I don’t know how to do is get the Access report to print the
first record on the 5th line, the second record to print on the 8th line, the
third record to print on the 9th line, the fourth record to print on the 11th
line, and then print the totals on the total line.


I would appreciate any help I can get on this issue.


Thanks,



Dennis