By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

Compute table difference

P: 19
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
Dec 17 '07 #1
Share this Question
Share on Google+
3 Replies


FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. 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
  2. UNION
  3. 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;
  4.  
Dec 17 '07 #2

P: 19
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:
Expand|Select|Wrap|Line Numbers
  1. 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 
  2. 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;
  3.  
However, this doesn't:
Expand|Select|Wrap|Line Numbers
  1. 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 
  2. 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;
  3.  
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
Dec 19 '07 #3

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO WerknemersAvailableDates ([WerknID], [Achternaam], [Date]) SELECT qryAllvsPlannedDiff.WerknID, qryAllvsPlannedDiff.Achternaam, qryAllvsPlannedDiff.[Date] FROM qryAllvsPlannedDiff;
  2.  
Dec 20 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.