Exclude specific records in a Union query

[Tskutnik]

I have a union query with 2 source tables
T1 with 150 records
T2 with 6 records

each table has 4 fields in each
MonthEndDate
AccountNumber
Value
SourceTableName

When I do the union query all 156 records result, but that is not what I want. If the same AccountNumber / MonthEndDate combination exists in both tables I only want the T2 record.

The result should be every record in T2, and only the records in T1 where the combination of MonthEndDate and AccountNumber are unique (do not have a match in T2).

This would be 150 records - 6 from T2, and 144 from T1.

Standard join conditions using all T2 will only give me the 6 T2 records and nothing from T1, so I need to use the Union (I think).

I tried some WHERE EXISTS and WHERE NOT EXISTS conditions but cannot quite get it.

If you could send back the lines of code I'd sure appreciate it. Have been playing with this for some time with no luck. Im sure it is something simple.

Thanks a bunch.

 

[MSAccessRookie]

What would happen if you did a LEFT JOIN of T1 to T2 ON AccountNumber and MonthEndDate, taking the values from T2 when AccountNumber and MonthEndDate exist, and taking the values from T1 when they do not exist?

 

[Tskutnik]

So I did a left join taking all records from T1 for AccountNumber and MonthEndDate (I think this is what you meant) and got 6 good records and a 144 blanks. Sorry - I am not sure how to do the second part - capture the T1 values if they do not exist in T2.

 

[MSAccessRookie]

So I did a left join taking all records from T1 for AccountNumber and MonthEndDate (I think this is what you meant) and got 6 good records and a 144 blanks. Sorry - I am not sure how to do the second part - capture the T1 values if they do not exist in T2.

The following (untested) code is a start toward what I was driving at. Note that Value is an SQL Reserved Word, and you should consider changing it.

SELECT IIf(t2.MonthEndDate IS NULL, t1.MonthEndDate, t2.MonthEndDate) AS MonthEndDate,
        IIf(t2.AccountNumber IS NULL, t1.AccountNumber, t2.AccountNumber) AS AccountNumber 
        IIf(t2.[B]Value[/B] IS NULL, t1.[B]Value[/B], t2.[B]Value[/B]) AS [B]Value[/B]
        IIf(t2.SourceTableName IS NULL, t2.SourceTableName, t2.SourceTableName) As SourceTableName
FROM t1 LEFT JOIN t2 ON ((t1.MonthEndDate = t2.MonthEndDate) AND (t1.AccountNumber = t2.AccountNumber))