Join Two Tables?

[Big Monkey]

I have two tables which are updated daily. There is a unique ID that can be found on both tables, but there isn't always a record for that same ID on both tables every day.

I need a query or to make a new table that will join the fields from both tables for each unique ID, even if the ID is missing from one table (leave that field null/blank)

Table1

03/11/09..Person2..Field_1
03/12/09..Person3..Field_1
03/13/09..Person4..Field_1

Table2

03/10/09..Person1..Field_2
03/12/09..Person3..Field_2
03/13/09..Person4..Field_2

I want the end result to be:

Table/Query

03/10/09..Person1..Field_1..Field_2
03/11/09..Person2..Field_1..Field_2
03/12/09..Person3..Field_1..Field_2
03/13/09..Person4..Field_1..Field_2


Hope that's clear enough. Any help?

 

[bobinnc]

Big Monkey,

Since the missing value can come from either table, you cannot use a standard join.

Try something like this:

SELECT Date, Person, Max(Field1) AS MaxOfField1, Max(Field2) AS MaxOfField2
FROM (SELECT Table1.Date, Table1.Person, Table1.Field1, "" AS Field2
FROM Table1
UNION SELECT Table2.Date, Table2.Person, "" AS Field1, Table2.Field2 
FROM Table2)
GROUP BY Date, Person;

It uses a sub query to union the values together with empty strings as the opposite field. Then it groups the resultant data by date and person, showing the max from the fields. As long as you only expect one value per day per person from either table, this should work. Good luck. - Bob
You may want to change the resultant field names.

 

[Big Monkey]

Awesome!!

Thank you very much!!!!