Compute table difference

[RobinDiederen]

Hi All,

I have to tables with an identical structure. I want to compute the difference between this tables (probably by using some SQL statement).

Question is, how to do so?

Layout of the tables: (SomeID, Name, Date). Primary key: the whole structure (no, SomeID can't be used for key, autonumber field will not work either).

I'd be glad to use an EXCEPT of MINUS construction, but these seem to be unsupported in Access.

I'd be happy to receive any advice..

Best, Robin

[FishVal]

Hi, Robin.

The following query unions records form [tbl1] not matching any record in [tbl2] with records form [tbl2] not matching any record in [tbl1]. To find unmatched record an outer join is used. Nz() function is used to resolve joins on fields containing Null.

SELECT tbl1.SomeID, tbl1.Name, tbl1.Date FROM tbl1 LEFT JOIN tbl2 ON Nz(tbl1.SomeID)=Nz(tbl2.SomeID) AND Nz(tbl1.Name)=Nz(tbl2.Name) AND Nz(tbl1.Date)=Nz(tbl2.Date) WHERE tbl2.SomeID Is Null AND tbl2.Name Is Null AND tbl2.Date Is Null
UNION
SELECT tbl2.SomeID, tbl2.Name, tbl2.Date FROM tbl2 LEFT JOIN tbl1 ON Nz(tbl1.SomeID)=Nz(tbl2.SomeID) AND Nz(tbl1.Name)=Nz(tbl2.Name) AND Nz(tbl1.Date)=Nz(tbl2.Date) WHERE tbl1.SomeID Is Null AND tbl1.Name Is Null AND tbl1.Date Is Null;
 

[RobinDiederen]

Hi there Fishval,

First of all I'd like to thank you for the nice and extensive comment.

Sadly enough, I cannot it to work entirely. I've replaced (textual replace) some values in your query. When I execute that Query, I get the results I want to have. That's good.

However, as soon as I make this query a subquery of an "INSERT INTO" statement, Access returns an error (Syntax ERROR in INSERT statement).

So this works:

SELECT WerknemersAllDates.WerknID, WerknemersAllDates.Achternaam, WerknemersAllDates.Date FROM WerknemersAllDates LEFT JOIN WerknemersPlannedDates ON Nz(WerknemersAllDates.WerknID)=Nz(WerknemersPlannedDates.WerknID) AND Nz(WerknemersAllDates.Achternaam)=Nz(WerknemersPlannedDates.Achternaam) AND Nz(WerknemersAllDates.Date)=Nz(WerknemersPlannedDates.Date) WHERE WerknemersPlannedDates.WerknID IS NULL AND WerknemersPlannedDates.Achternaam IS NULL AND WerknemersPlannedDates.Date IS NULL 
UNION SELECT WerknemersPlannedDates.WerknID, WerknemersPlannedDates.Achternaam, WerknemersPlannedDates.Date FROM WerknemersPlannedDates LEFT JOIN WerknemersAllDates ON Nz(WerknemersAllDates.WerknID)=Nz(WerknemersPlannedDates.WerknID) AND Nz(WerknemersAllDates.Achternaam)=Nz(WerknemersPlannedDates.Achternaam) AND Nz(WerknemersAllDates.Date)=Nz(WerknemersPlannedDates.Date) WHERE WerknemersAllDates.WerknID IS NULL AND WerknemersAllDates.Achternaam IS NULL AND WerknemersAllDates.Date IS NULL;
 

However, this doesn't:

INSERT INTO WerknemersAvailableDates ([WerknID], [Achternaam], [Date]) SELECT WerknemersAllDates.WerknID, WerknemersAllDates.Achternaam, WerknemersAllDates.Date FROM WerknemersAllDates LEFT JOIN WerknemersPlannedDates ON Nz(WerknemersAllDates.WerknID)=Nz(WerknemersPlannedDates.WerknID) AND Nz(WerknemersAllDates.Achternaam)=Nz(WerknemersPlannedDates.Achternaam) AND Nz(WerknemersAllDates.Date)=Nz(WerknemersPlannedDates.Date) WHERE WerknemersPlannedDates.WerknID IS NULL AND WerknemersPlannedDates.Achternaam IS NULL AND WerknemersPlannedDates.Date IS NULL 
UNION SELECT WerknemersPlannedDates.WerknID, WerknemersPlannedDates.Achternaam, WerknemersPlannedDates.Date FROM WerknemersPlannedDates LEFT JOIN WerknemersAllDates ON Nz(WerknemersAllDates.WerknID)=Nz(WerknemersPlannedDates.WerknID) AND Nz(WerknemersAllDates.Achternaam)=Nz(WerknemersPlannedDates.Achternaam) AND Nz(WerknemersAllDates.Date)=Nz(WerknemersPlannedDates.Date) WHERE WerknemersAllDates.WerknID IS NULL AND WerknemersAllDates.Achternaam IS NULL AND WerknemersAllDates.Date IS NULL;
 

I've tried to play a bit with brackets (to revent scoping errors), however, that doesn't do the trick (or I'm doing it wrong).

Could you perhaps tell me what's going wrong?

Thanks again, Robin

[FishVal]

Hi, Robin.

Try to simlify expression.
Save the first query - let us say it will be [qryAllvsPlannedDiff].
Then use it as source dataset in the second query

INSERT INTO WerknemersAvailableDates ([WerknID], [Achternaam], [Date]) SELECT qryAllvsPlannedDiff.WerknID, qryAllvsPlannedDiff.Achternaam, qryAllvsPlannedDiff.[Date] FROM qryAllvsPlannedDiff;