Help with Union

[Nathan]

I'm new to this... hence the difficulties.

I have two tables each with two fields. The first field is a date (mm/dd/yy)
the second field is a number (single digit integer). I want to combine the
two tables so that the dates and their associated numbers go together in one
query rather than two tables. The only stipulation is that I don't want
multiple dates. So, if a date exists in table1 then grab that date and its
number, but only grab the date and number from table2 if the table2 date
doesn't exist in table1... confusing enough?

If I put the following into SQL, the date part works:

SELECT Table1.Date1
FROM Table1
UNION
SELECT Table2.Date2
FROM Table2

When I add the number portion it falls apart:

SELECT Table1.Date1, Table1.Number1
FROM Table1
UNION
SELECT Table2.Date2, Table2.Number2
FROM Table2

I get double date entries presumably because the associated numbers are
different. I want the dates in table1 to take presidence regardless of the
associated numbers. If a date exists in table1 then take date1 and number1,
if a date exists in table2 that is not in table1 then take date2 and number2.

Any help would be greatly appreciated. Thanks for your time!

Nathan

 

[Jerry Whittle]

SELECT Table1.Date1, Table1.Number1
FROM Table1
UNION
SELECT Table2.Date2, Table2.Number2
FROM Table2
WHERE Table2.Date2
Not In (SELECT Table1.Date1
FROM Table1) ;

 

[Nathan]

Thanks Jerry!

I did try this earlier (and again just now) but I get 'Query is too complex'
as a return.

Weird!?

Thanks again.