Combining 2 tables in 1 Query Result

[kev100 via AccessMonster.com]

I've got 2 tables containing Time Stamped entries on employees.

One table has a mass of entries.........several entries an hour...all in
order by TIME.

The Second table also has Time Stamped entries for employees....but only a
FEW every now and then.....like 2-3 over a five hour period. The only common
element between the 2 is Date and Employee ID


The query would need to reference both tables. Since the TIME is unique
(there will never be a record with the same Time Stamp).....the list would
need to simply show:

Employee Login Time (from table 1) Employee ID (from table 1) Employee
Name (from table 1)
Employee Login Time (from table 2)
Employee Login Time (from table 1) Employee ID (from table 1) Employee
Name (from table 1)
Employee Login Time (from table 1) Employee ID (from table 1) Employee
Name (from table 1)
Employee Login Time (from table 1) Employee ID (from table 1) Employee
Name (from table 1)
Employee Login Time (from table 2)
Employee Login Time (from table 1) Employee ID (from table 1) Employee
Name (from table 1)
Employee Login Time (from table 1) Employee ID (from table 1) Employee
Name (from table 1)

.....in a list...in order by TIME. So......the query results would look
something like:

Login Time1a____Employee ID___Employee Name____
Login Time1b____Employee ID___Employee Name____
Login Time1c____Employee ID___Employee Name____
Login Time1d____Employee ID___Employee Name____
Login Time1e____Employee ID___Employee Name____
Login Time2A____Employee ID___Employee Name____
Login Time1f____Employee ID___Employee Name____
Login Time1g____Employee ID___Employee Name____
Login Time1h____Employee ID___Employee Name____
Login Time1i____Employee ID___Employee Name____
Login Time1j____Employee ID___Employee Name____
Login Time1k____Employee ID___Employee Name____
Login Time2B____Employee ID___Employee Name____
Login Time1j____Employee ID___Employee Name____
Login Time1l____Employee ID___Employee Name____
....etc.

The question is....I'm not sure how to create this query.....since
information from BOTH tables should NOT appear One line.....but on seperate
lines....since the entries are being put in order by TIME.

Hope this makes sense.

Thanks,
Kev

 

[John W. Vinson]

I've got 2 tables containing Time Stamped entries on employees.

One table has a mass of entries.........several entries an hour...all in
order by TIME.

No, it doesn't.

A Table HAS NO ORDER. It's an unordered "bucket" of data.

The Second table also has Time Stamped entries for employees....but only a
FEW every now and then.....like 2-3 over a five hour period. The only common
element between the 2 is Date and Employee ID


The query would need to reference both tables. Since the TIME is unique
(there will never be a record with the same Time Stamp).....the list would
need to simply show:
<snip>
The question is....I'm not sure how to create this query.....since
information from BOTH tables should NOT appear One line.....but on seperate
lines....since the entries are being put in order by TIME.

A UNION query will do this for you:

SELECT [Employee Login Time], [Employee ID], [Employee Name]
FROM Table1
WHERE <some criteria>
UNION ALL
SELECT [Employee Login Time], [Employee ID], [Employee Name]
FROM Table2
WHERE <some criteria>
ORDER BY [Employee Login Time];

Your example doesn't indicate whether Table2 contains the employee ID and name
(and ideally the employee name should not be in EITHER table, only in the
Employees table) - does it?

John W. Vinson [MVP]

 

[kev100 via AccessMonster.com]

Thanks very much !

Your example doesn't indicate whether Table2 contains the employee ID and name
(and ideally the employee name should not be in EITHER table, only in the
Employees table) - does it?

John W. Vinson [MVP]

The 2nd table only has Employee ID and Login Time (did not review the example
before posting).

Is it okay for there to be different fields in each table (in addition to the
common ones)?

Thanks

 

[John W. Vinson]

Thanks very much !


The 2nd table only has Employee ID and Login Time (did not review the example
before posting).

Where can the employee name be found? Is there a separate Employee table?

Is it okay for there to be different fields in each table (in addition to the
common ones)?

The two SELECT clauses must have the same number of fields, and they must
match in datatype. I'd suggest either

SELECT [Employee Login Time], [Employee ID], [Employee Name]
FROM Table1
WHERE <some criteria>
UNION ALL
SELECT Table1.[Employee Login Time], Table2.[Employee ID], Employees.[Employee
Name]
FROM Table2 INNER JOIN Employees
ON Table2.[Employee ID] = Employees.[Employee ID]
WHERE <some criteria>
ORDER BY [Employee Login Time];

or else leave the employee name out of these two SELECTs and just create
another query joining the UNION query to the employee table to pick up the
names.

John W. Vinson [MVP]

 

[kev100 via AccessMonster.com]

Thanks again,...

Where can the employee name be found? Is there a separate Employee table?

The first table is actually a link to a SQL view (which a combination of 3
SQL tables...one being the employee table).

But....for Access.....it appears as just 1 primary table.

Is it okay for there to be different fields in each table (in addition to the
common ones)?

The two SELECT clauses must have the same number of fields, and they must
match in datatype. I'd suggest either

SELECT [Employee Login Time], [Employee ID], [Employee Name]
FROM Table1
WHERE <some criteria>
UNION ALL
SELECT Table1.[Employee Login Time], Table2.[Employee ID], Employees.[Employee
Name]
FROM Table2 INNER JOIN Employees
ON Table2.[Employee ID] = Employees.[Employee ID]
WHERE <some criteria>
ORDER BY [Employee Login Time];

or else leave the employee name out of these two SELECTs and just create
another query joining the UNION query to the employee table to pick up the
names.

I will give this a try.....thanks very much !

Kev