two way table comparison with nulls from both sides? 1

[MePenguin]

I can do this in VBA (code below) but am sure queries would be more efficient... (skip to the bottom if you just need to see the desired result!)

I have two select queries both of which extract two common fields from different tables (table1 and table2). One field is essentially the key field for both queries (SampleCODE), and the other is a user input Sample Name (RefNrContext). In a comparison on SampleCODE, RefNrContext can be null or different in either table.

SELECT distinct Table1.SampleCODE, Table1.RefNrContext _FROM Table1 ORDER BY Table1.SampleCODE;

SELECT Table2.SampleCODE, Table2.RefNrContext FROM Table2 _ORDER BY Table2.SampleCODE;

Table1 contains original sample data, and Table2 contains edited sample data where samples may have been added, deleted, or had their RefNrContext changed (see example below). SampleCODE's are never changed, but samples can be deleted or added, and the user may decide to move sample names around so that they end up assigned to different SampleCODEs.

For each SampleCODE I need to know the Table1 RefNrContext and Table2 RefNrContext, and where there are no matches in each direction.
The code below uses DAO to put the comparisons into an array:
rsTable1= recordset from 1st query... etc
KeyFieldName = "SampleCODE"
ComparisonFieldName="RefNrContext"

For rsCounter = 1 To rsTable1.RecordCount
    ArrDifferences(0, rsCounter - 1) = rsTable1(KeyFieldName)
    ArrDifferences(1, rsCounter - 1) = rsTable1
_(ComparisonFieldName)
    SQLString = KeyFieldName & "='" & ArrDifferences(0, 
_rsCounter - 1) & "'"

    rsTable2.FindFirst SQLString
    If rsTable2.NoMatch Then
        ArrDifferences(2, rsCounter - 1) = ""
    Else
        ArrDifferences(2, rsCounter - 1) = rsTable2
_(ComparisonFieldName)
    End If
    
    rsTable1.MoveNext
    rsTable2.MoveNext
Next rsCounter

    'continue to loop table2 recordset if larger
    If rsTable2.RecordCount > rsTable1.RecordCount Then
        For rsCounter = rsTable1.RecordCount + 1 To _rsTable2.RecordCount + 1
            ArrDifferences(0, rsCounter - 1) = rsTable2_(KeyFieldName)
            ArrDifferences(1, rsCounter - 1) = ""
            ArrDifferences(2, rsCounter - 1) = rsTable2_(ComparisonFieldName)
            rsTable2.MoveNext
        Next rsCounter
    End If

Example results:
[tt]
samplecode table1 table2
SAMP001877 S199 S204
SAMP001878 S200 S200
SAMP001879 S204
SAMP001880 S206 S199
SAMP001881 S208 S999_1
SAMP001882 S214 S214
SAMP001883 S219 S219
SAMP001884 S571 S571
SAMP002481 s57_1
SAMP002482 s57_1a
[/tt]

I then use more VBA to apply changes to table1 (then create a crosstab where RefNrContext become the column headings).

This should all be SQLable, shouldn't it???

Any ideas?



Phil

---------------
Pass me the ether.

 

[PHV]

something like this ?
SELECT A.SampleCODE, A.RefNrContext AS Value1, B.RefNrContext As Value2
FROM Table1 As A LEFT JOIN Table2 AS B ON A.SampleCODE=B.SampleCODE
UNION SELECT B.SampleCODE, A.RefNrContext, B.RefNrContext
FROM Table1 As A RIGHT JOIN Table2 AS B ON A.SampleCODE=B.SampleCODE
WHERE A.SampleCODE Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MePenguin]

Perfect! Have a star *

And a hell of a lot simpler.
Bit of a pity though, I do like loops & arrays...

Thanks

Phil

---------------
Pass me the ether.

 

[MePenguin]

A follow-up question if you don't mind:

Can I run an update query on table1 using the value2 result of your sql as the "Update to" source for A.RefNrContext? Sounds like a circularity to me, if these things run in realtime.

If not, and I'm getting an "Operation must use a query that can be updated" error (although in Swedish, 'cos my Office is comfused...), what's the best solution?

a) getrows the sql into an array and loop it (told you I like loops) DAO etc
b) make table query it and then run the update
c) something clever that I haven't thought of
d) the error isn't real, figure out what it really means

Regards,


Phil

---------------
Pass me the ether.

 

[MePenguin]

PS. Update, append and delete.

Phil

---------------
Pass me the ether.