Access Query Help Needed

[Sockpuppet]

I have 2 queries I am trying to combine here.

I have 2 tables.

Basically:

Table1:
Date
EmployeeCode
Hours_On_TA_System

Table2:
Date
EmployeeCode
Hours_On_WMS_System


I'm trying to combine the two so that I get a list of:

Date
Employee Code
Hours_On_WMS
Hours_On_TA


The problem with doing a join is that employees may appear in either table1, table2 or both.

With joins I can either show those in table1 matching to those in table 2. Or those in table 2 and the matching records from table 1.

There may be people who are in table 1 and not table 2 but also those that are in table 2 but not 1 at the same time.

Any ideas...I seem to remember something about Union queries but I can't see if this is what I need.

Cheers
Sockpuppet

 

[TimberWolf]

Sounds like UNION ALL is what you're after.

 

[Spacecadet]

Like this?

Date   Employee Code Hours_On_WMS Hours_On_TA
01/01 100001            2.5
01/02 100002                                  8
01/02 100003            6.5
01/02 100004            3                    5.5

In a proper system you need a Full outer join... but Access doesn't support this
Have a google, there are ways of simulating a full outer join
http://bytes.com/forum/thread190756.html

Alternatively... create a numbers/dates table and use 2 left outer joins

 

[PRC1964]

This should do it:

SELECT Table1.Date, Table1.EmployeeCode, Table1.Hours_On_TA_System, 0 AS Hours_On_WMS_System
FROM Table1
UNION
Select Table2.Date, Table2.EmployeeCode, 0 As Hours_On_TA_System, Table2.Hours_On_WMS_System
FROM Table2

 

[Sockpuppet]

Cheers guys.

Help much appreciated.

 

[TimberWolf]

This should do it:

SELECT Table1.Date, Table1.EmployeeCode, Table1.Hours_On_TA_System, 0 AS Hours_On_WMS_System
FROM Table1
UNION
Select Table2.Date, Table2.EmployeeCode, 0 As Hours_On_TA_System, Table2.Hours_On_WMS_System
FROM Table2

How's about summing by EmployeeCode (for all dates - danger!). It's been a while, just clearing away cobwebs...

SELECT tmptable.EmployeeCode, Sum(tmptable.TA_HOURS) AS SumOfTA_HOURS, Sum(tmptable.WMS_HOURS) AS SumOfWMS_HOURS
FROM [SELECT  Table1.EmployeeCode as EmployeeCode, Table1.Hours_On_TA_System as TA_HOURS, 0 AS WMS_HOURS
      FROM Table1
      UNION ALL
      Select  Table2.EmployeeCode  as EmployeeCode, 0 As TA_HOURS, Table2.Hours_On_WMS_System as WMS_HOURS
      FROM Table2
   ]. AS tmptable
GROUP BY tmptable.EmployeeCode;