Query comparing multiple tables

[slam41]

I want to create a query for employees that reads information from different
tables. For example one table involves payroll issues and one table involves
attendance issues. One employee may have had a payroll issue but not a
attendence issue while the next employee may have had attendence issues but
no payroll issues. How do I create the query that shows each employee and
which issues they may have had?

 

[vanderghast]

In a new query, bring the table having all the employee (without duplicated
values), then, the two other tables. Join each of these other tables with
the table with all employee, edit the join so to always have all records
from the table with all employee. Drag any field you want from the other
tables (in addition with the employee id/name from table of all employee) in
the grid.


Vanderghast, Access MVP

 

[Tom van Stiphout]

On Sun, 2 Aug 2009 09:08:01 -0700, slam41

Assuming those issues are in different tables, use a union query:
select myFields from myTable1
union
select myFields from myTable2
The number of fields and their datatypes need to be the same in both
queries. I often find it handy to add a TableName column:
select "myTable1" as TableName, myFields from myTable1
union
select "myTable2 as TableName, myFields from myTable2

-Tom.
Microsoft Access MVP