Report based on a number of queries

[Chris C]

I am trying to create a report based on using information from on number of
queries i have created.

My first query links a "Base Vehicle Information" table to a "Oil change"
table. The purpose of the query is to provide the dates of when any chosen
vehicle was given an oil change.

My second query links the same "Base Vehicle Information" table to a
"Activities" table. The purpose of this query is to provide the dates of when
any chosen vehicle was serviced.

In my report i would like to be able to use these queries to provide any
selection of data that i require. For example: The dates of when Vehicle#3
had its oil changed, and in a seperate section, the dates when Vehicle#7 was
serviced.

How do i introduce two queries into a report layout without producing
results which are untrue? - I cannot have linke the two queries correctly
since the data shown in the "Data Sheet" of the report's "Query Builder",
produces every possible combination of the data in both queries.

In the report "Design View" do i need to create a different "Detail" or
section for each query that i wish to introduce?

If anyone can help, thank you!

 

[Allen Browne]

Use a subreport to handle the 2nd set of details.

1. Create a query that combines tables [Base Vehicle Information] and [Oil
Change].

2. In the upper pane of the query design window, double-click the line
joining the 2 tables. Access opens a dialog with 3 options. Choose the one
that says:
All records from [Base Vehicle Information], and any matches from [Oil
Change].
If you skip this step, your report will not include vehicles that have not
had an oil change.

3. Create the report based on this query.

4. In report design view, open the Sorting And Grouping box (View menu.)
Select the primary key of the the [Base Vehicle Information] table, and in
the lower pane of this dialog choose Yes for both Group Header and Group
Footer. Access adds these sections to the report.

5. Put the fields for the vehicle into the Vehicle ID Header section, and
the fields from the Oil Change into the Detail section.

6. In the Vehicle ID Footer section, add a Subreport from the Toolbox (View
menu.) The subreport gets its records from the Activities table.

The subreport acts as a 2nd set of detail records for the vehicle.