Choose report/layout according to a record field value

[ggtektips]

I have been given the 3 layouts I must use and each is contained in its own report . My job is to create a query that captures the appropriate records. Then depending on a field value in each of those records, I must choose the appropriate layout for that record.

I want to automate the "choose the appropriate report/layout" part of the process so that all I have to do is drop the query name into a report property and have the correct report/layout used for each record in the query.

I want to run one report that runs all 3 of the reports I have been given to use, putting the results into one .pdf.

 

[sxschech]

Is this one query that you are using or 3 separate queries and 3 separate reports?
Will the be reports be run by clicking a button from a form?

 

[dhookom]

In addition to sxschech's question, what's unique/different about the layouts? Are you just moving some controls around in the detail section or do you expect major changes in the appearance?

Duane
Hook'D on Access
MS Access MVP

 

[ggtektips]

Is this one query that you are using or 3 separate queries and 3 separate reports?
The current way is with 3 separate queries and 3 separate reports. This is what I want to eliminate. I can write one query to capture all the records. I want to use that query in one "smart" report. That report would handle what the current 3 do.

Will the be reports be run by clicking a button from a form? No, but it could if need be. I was just going to run it by choosing "Print Preview" as I run most reports.

Are you just moving some controls around in the detail section or do you expect major changes in the appearance?
Major changes are possible. I must use the reports I'm given. The only guarantee I have is that the reports will not require any values that I can't supply with the query.

 

[sxschech]

What will determine how the report looks (Numeric, Text Description)? Is the report displaying less/more information, does it need to be in landscape for one and portrait for another? Is it that the headers or titles only need to change based on the query values or do actual data change (Grouping, Summary, Detail)

 

[ggtektips]

Different information is displayed on the three reports but they all have portrait orientation. Each record is data on a piece of equipment. Each record is used to create a one page form that is filled out by a technician as he tests the equipment. So you could imagine that the form details for testing a pump are different then for testing a valve.

 

[sxschech]

You mentioned that there will be 3 versions(variations) of the report. Perhaps an example such as below?

Version 1

NAME Type TEST1 TEST2 TEST3
Equipment A Pump 55 66 33
------------------------------------------------

Version 2
Monitor Check
Equipment B Valve 44
26
Checked
------------------------------------------------

Version 3

??
------------------------------------------------

What in the query will determine how the report is displayed, the fact that it is a Valve vs a Pump, a code?

 

[dhookom]

So you could imagine that the form details for testing a pump are different then for testing a valve.

I imagine tables:
tblEquipment (one record per equip)
tblTestTypes (one for each unique type of test)
tblEquipTests (one record for each type of test for each equip)
tblTest (one record for each test event: date, who,....)
tblTestResults

This would be a normalized solution that would use exactly the same tables for tests of all equipment.

Duane
Hook'D on Access
MS Access MVP

 

[ggtektips]

Yes szschech, you got the idea. One sheet for each piece of equipment.

There is one table, tblEquipment, that is updated by others and given to me. There is a field in each record that I could use to determine which report to use. The reports are also given to me. I need to use the right report for each record and I was hoping to do it without haveing to write/modify 3 queries and 3 reports each time I need to make a set of these equipment test sheets.