identfiying missing data across 2 tables

[lazy_scrapper]

Hi i have a question in regards to how would i create a query that would produce the results for the following tables:

My expected errors across these two tables are:
Effectivity 9 for A000103 rev 2 in Table 1 is not present in Table 2.
Effectivity 5 and 6 for A000103 rev 2 in Table 2 not present in Table 1.

Have tried the following to identify whats different but I do not get the results i want.

One query that inner joins on SA_ID and SA_REV only:
SELECT Table1.SA_ID, Table1.SA_REV, Table1.EFFECTIVITY, Table2.EFFFROM, Table2.EFFTO
FROM Table1 INNER JOIN Table2 ON (Table1.SA_REV = Table2.SA_REV) AND (Table1.SA_ID = Table2.SA_ID)
WHERE (((Table1.EFFECTIVITY) Not Between [Table2].[EFFFROM] And [Table2].[EFFTO]));

 

[namliam]

Try using the unmatched query wizard

 

[lazy_scrapper]

The unmatched query helpes me to match like attributes between tables.

Problem is with my tables table1 has single attribute "effectivity" and table2 has multiple attributes to supply the same data in a different format. Access db attached original post.

would i be better to create seperate queries then UNION??? clutching at straws?

 

[lazy_scrapper]

thanks for your help, it made me think outside the box. Dont know if its the prettiest way to do this but it works. Hoping it works over a database with thousands of records.

If there is a smarter way to do it please provide comments, as they always assist, even if its to push thoughts outside my little box.


Thanks again

 

[namliam]

Well its still an unmatched query, only additional problem you have is you have to unify the data one way or another.

So first make a (potentially) union query to make your table1 resemble your table2 or vice versa.
Then unumatch query the query with the comparable table

 

[namliam]

thanks for your help, it made me think outside the box. Dont know if its the prettiest way to do this but it works. Hoping it works over a database with thousands of records.

If there is a smarter way to do it please provide comments, as they always assist, even if its to push thoughts outside my little box.


Thanks again

Sorry to say but this dont work, dont work at all ! Your along the rigth lines, but this is broken

 

[namliam]

OK, I taken up the gauntlett....
You will need this function:

Public Function SplitIt(ThisText As String, ReturnIt As Integer) As String
    On Error GoTo Errorout
    SplitIt = Split(ThisText, ",", 99, vbTextCompare)(ReturnIt - 1)
    
    Exit Function
Errorout:
    SplitIt = "ERROR"
End Function

In any public module

Then you will need a (big) union query

SELECT Table1.SA_ID, Table1.SA_REV, Table1.EFFECTIVITY, SplitIt([Effectivity],1) AS [Values] FROM Table1 WHERE SplitIt([Effectivity],1)<>"ERROR"
Union
SELECT Table1.SA_ID, Table1.SA_REV, Table1.EFFECTIVITY, SplitIt([Effectivity],2) AS [Values] FROM Table1 WHERE SplitIt([Effectivity],2)<>"ERROR"
Union
SELECT Table1.SA_ID, Table1.SA_REV, Table1.EFFECTIVITY, SplitIt([Effectivity],3) AS [Values] FROM Table1 WHERE SplitIt([Effectivity],3)<>"ERROR"
Union
Etc...

Then use this union query to do what you did with the between thing...

NOTE this will only work properly with 1 character fields, if you have values like 11, 15,99 or 01 etc... this again will fail....

 

[lazy_scrapper]

Appologies namliam, did not close db, before attaching file. %^%*. Copy attached with what I did.

Again thanks

Have tried to work out how to use the function bit, not actually sure where to put it? Im only a novice who is very dependant on query builder.
-------
Public Function SplitIt(ThisText As String, ReturnIt As Integer) As String
On Error GoTo Errorout
SplitIt = Split(ThisText, ",", 99, vbTextCompare)(ReturnIt - 1)

Exit Function
Errorout:
SplitIt = "ERROR"
End Function

 

[namliam]

OK find attached your db with solution queries

Solution1 isnt perfect but works more or less

Solution2 is fairly nice I think.

 

[lazy_scrapper]

Thanks heaps. I will apply them to the real data and see what happens.

Thanks Again