Query to combine two tables 1

[saraann422]

I have two tables: one is for employees to record time spent outside of their main job objectives and the second is for supervisors to record absences. Both tables have the same fields and I want a query that combines the two and lists them.

ex.

John 1/1/08 2 hrs phone
John 1/2/08 8 hrs vacation
Sally 1/1/08 3 hrs meeting
Sally 1/1/08 1 hr phone
Sally 1/2/08 8 hrs sick

Can someone tell me how to do this?

Thank you!
Sarah

 

[JBinQLD]

G'day. What your looking for is called a union query. An example would be:

a) Create an SQL statement, or query to display each set first such as

SELECT tblMainObj.Name, tblMainObj.Date, tblMainObj.Duration, tblMainObj.reason FROM tblMainObj;

and

SELECT tblAbsense.Name, tblAbsense.Date, tblAbsense.Duration, tblAbsense.reason FROM tblAbsense;


b) Create a new query and in SQL view paste the two queries above in, remove the ; from the first and add word UNION between:

SELECT tblMainObj.Name, tblMainObj.Date, tblMainObj.Duration, tblMainObj.reason FROM tblMainObj UNION
SELECT tblAbsense.Name, tblAbsense.Date, tblAbsense.Duration, tblAbsense.reason FROM tblAbsense;

c) Save / view your finished union query.

d) Too easy, no dramas, all done

 

[fneily]

Have you read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Your tables are not normalized. You basically have Excel spreadsheets, not Access tables.

You should have something like:
tblEmployees
EmployeeID
other employee fields

tblOutsideDuties
ODID
Description

tblNonJobs
NJID
EmployeeID
ODID
Date
Duration

Now what you want falls right out. You shouldn't have to do all sorts of unnecessary queries if your tables are constructed by the rules of normalization.