combining data from different reports 1

[heathlovell]

Hello,
What is the best way to combine data from different reports? For example, I have a one query/report that sums the production hours for the month for the different production units that we have. I have another query/report that has a detail line for each time a piece of equipment went down. I created a report that shows how long each piece of equipment was down for the month, but I want to add on how long the unit ran for the month so I can calculate % availability. Thanks for the help.
Heath

 

[dhookom]

You can use subreports on your main reports. This allows you to use several record sources in one main report.

Duane
MS Access MVP

 

[heathlovell]

Is there any way to get numbers (such as Grand Totals) from macros or VBA? Can I create a a query with VBA in the report to grab certain information?

Thanks,
Heath

 

[dhookom]

Tell us where your numbers are and we might be able to tell you how to grab their values for use elsewhere. Also, tell use where you want to use the numbers.

I would never, ever have one report rely on values from a separate report. I might have values on a main report that are extracted from a subreport.

Duane
MS Access MVP

 

[heathlovell]

I have one table that has dates, unit and production minutes
Example:
10/1/03, 1 unit, 465 (minutes)
10/1/03, 2 unit, 465
10/2/03, 1 unit, 510

I have another table that has the delays.
Example:
M1, Electrical, 10 (minutes)
M2, Mechanical, 5

I have a query that takes the delay table and only grabs the records for a certain date range. It uses the [Enter start date] on the criteria of the query. My report then groups the data by equipment and then by delay type.

I have a couple of problems:
1) From the report, how can I grab the start and end date I entered for the query?
2) On the footer for each piece of equipment, how can I get the total number of minutes that unit has ran for the period of time? Do I need to create a function that will run a query based on the unit and date range and return the minutes?

Thanks for your time and help,
Heath

 

[dhookom]

1) Don't use parameter queries. Use text boxes on a form so your report's record source query will have a criteria like:
Between Forms!frmA!txtStartDate and Forms!frmA!txtEndDate

2) You mention "equipment" but I don't see an equipment field or how your one table is related to your other table.

Duane
MS Access MVP

 

[heathlovell]

How do I get from the form to the report? I can picture creating a form with two text boxes on them for the dates. Do I just leave the text box open and then run the report? What do I set the source for the report to? Where do I put the criteria?

The two tables are related by the equipment being on a certain production unit. I do not have a table that relates the two.

 

[dhookom]

You must have the form open and replace your report query parameter(s) with references to the controls. ie:
Between Forms!frmA!txtStartDate and Forms!frmA!txtEndDate

You need to provide some actual field names for your tables and how they are related. It is impossible to help you without a clearer understand of your tables and how you want them reported.

Duane
MS Access MVP