One report from 3 tables, entering criteria only once

[ChuckCasey]

I have 3 unrelated tables with the following fields:

table1 table2 table3

WireNo RecordNo RecordNo
SDate Sdate Sdate
EmpNo EmpNo EmpNo
XferAmt Amt OrAmt
Fee Fee Fee

I need to create one report that has three sections, one for each table. The user will enter a beginning and end date and the EmpNo. I only want them to enter the information once, not three times.
I got way off track by creating a union query to combine the tables, which allowed the criteria to be entered only once, then figured out I couldn't separate the data into the sections I wanted.
Can someone please point me in the right direction? Thanks.

 

[RoyVidar]

I would experiment whith the following

- use a form to enter the parameters, then reference those form controls from each of the queries (i e in the criteria row forms!frmMyForm!txtStartDate)
- use three? reports - one main, and two subreports

Roy-Vidar

 

[lespaul]

then figured out I couldn't separate the data into the sections I wanted.

Is this because you need it separated by table and the table name wasn't in your data?

Add a field to each query that refers to it's original table:

SELECT 'Table 1', SDate, Empno, XFerAmt FROM Table 1
UNION
SELECT 'Table 2', ....

HTH


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design