Combine 3 queries for report

[dreamsoul620 via AccessMonster.com]

Hi,
This sounds simple enough. My queries have a few of the same fields, but do
not have the same data. Each query pulls from a separate table. I want to
be able to give the user a report that pulls the data from all three of these
queries into one main report ( a history of a specific employee or department)
.. I have one query with a parameter for Employee name (I'll make the one for
department after I've figured this one out) so the use is prompted for the
name when the report is opened. If the three queries can be combined as the
report is run or before, this should query all three to pull all info where
Employee Name = entered data. It would be easy if they all contained the
same names, but they do not. It's possible that one query may contain
Employee A, query two contain Employee A and Employee C, and query three
contain Employee F and K. If anyone has any ideas on how to do this, please
let me know.

Another idea was to put each query result in a separate report and have them
display on separate pages based on the one parameter for Employee Name.

Thanks.

 

[Allen Browne]

The best solution would probably be to combine the 3 tables into one, with a
new field to distinguish whatever is the difference between the 3.

If you cannot do that, it is possible to use a UNION query to combine the
records from your 3 queries into one long list. You cannot see this query
grapically, but it is not difficult in SQL view.

1. Create a new query.

2. Switch to SQL View (View menu.)

3. Enter something like this:
SELECT Query1.* FROM Query1
UNION ALL
SELECT Query2.* FROM Query2
UNION ALL
SELECT Query3.* FROM Query3;

This assumes that all 3 queries have the same number of fields, of the same
type, in the same order.

 

[dreamsoul620 via AccessMonster.com]

Thanks. I'll try pulling them into one table. Or at least create the
queries to combine the data somehow, then create a query to pull from the
main table. As is, I have queries and reports off of these "sub" tables and
would prefer not to have to change the entire structure. Once again, thanks
for the help

The best solution would probably be to combine the 3 tables into one, with a
new field to distinguish whatever is the difference between the 3.

If you cannot do that, it is possible to use a UNION query to combine the
records from your 3 queries into one long list. You cannot see this query
grapically, but it is not difficult in SQL view.

1. Create a new query.

2. Switch to SQL View (View menu.)

3. Enter something like this:
SELECT Query1.* FROM Query1
UNION ALL
SELECT Query2.* FROM Query2
UNION ALL
SELECT Query3.* FROM Query3;

This assumes that all 3 queries have the same number of fields, of the same
type, in the same order.

Hi,
This sounds simple enough. My queries have a few of the same fields, but

[quoted text clipped - 24 lines]

Thanks.