Help with approach to report

[Dennis]

Hi,

The charity I work for received money from different government grants. We
have to submit reports government agencies using their formats. The point of
this statement is there is no changing the report.

I have two tables; tblProject and tblProjectDetail.

The tblProject has one record / row per project. The tblProjectDetail has
one record / row per thing / activity that was done to the project. At this
point in time, I have five project that fall under this reporting requirement.

The government defined report is formatted as such:


First section of report contains 16 fields from the project record.

The next section of the report lists all of the activity for a specified
period of time (usually a month) for that current project. Each activity
will take three lines on the report. There can be as few as one activity per
month, but the norm is about 30 different activities per month. As a result,
this section can be only a couple of lines or it can run for multiple pages.

If there is no activity for the reporting period, I can either skip the
report for that project of print a project report with no activity. An
activity falls in a reporting period if the activity’s “date occurred†falls
within the user specified beginning and end date.

The final section of the report contains about another 20 fields from the
project report.

So my question is, how do I create this report?

Now that I think about it, I’m guessing that I would want to make the
report’s source be a query based on the tblProject table. This record would
complete the data on the first and third section of the report.

The second section would be a sub-form that “joined†to the main form by
project number (which is in both tables). Only I don’t know how to limit the
data in the sub-form to the user specified time period based.

I would appreciate any pointers on starting this report. I’m not quite sure
how to approach it, especially limiting the activity data to the user defined
date range.

Thanks,

Dennis

 

[Duane Hookom]

Why not base your report on a query that contains both tables?

 

[Dennis]

Duane,

I can, but if I do that how do I print the top and bottom section of the
report, which deals with the project, only once while the middle section of
the report prints out each activty record?

That is the part that I don't know how to do


The report contains a multi-page print out for each project.
The multi-page print out contains a section where I print out project
info.
This is about 10 line.

The next section of the multi-page print out is a continuous listng of all
activity that has occured on project. This section can run multiple
pages.

That last section contains about 15 lines of project information.

Then the next project report starts.


Are you see where the Page Heading can be the first part of the project
report and the detail section can be the activity, but where would I put the
last part of the Project information. It can not be on the page footing
because that section of the report does not appear on each page, just at the
end of the project section.

 

[Duane Hookom]

You can create group header and footer sections based on the project record.
Place all of the project information in these sections.

If that doesn't work, you could base the main report on the Project record
and use a subreport (much like a subform) for the details.

 

[Dennis]

Duane,

Ok, I did not know about the group header and footer. I'll give that a shot
first.

Thanks,

 

[Dennis]

Duane,

It worked great! I had to play with report footing and heading and footing
requirement are different for the first and second page, but everything
worked great.