Unmatching Query Help

[MTSPEER]

Hello,

I'm trying to compare these two tables Table1 and Table2. They have the same field names and there are 200 fields for each table. But I need to compare 100 of the fields to see if there are any unmatching records. And I feel like I will get an error saying the query is too complex. Is there any suggestions on how I should do this?

Thanks

[CJ_London]

have multiple left joins between table1 and table2 on the fields you want match (max 16 joins) - any that don't will have nulls in table2

try using the query wizard - unmatched query

[MTSPEER]

I want to match all 100 fields to find any mismatches between the two tables.

[CJ_London]

have multiple queries as above

alternatively join on a unique ID and have multiple criteria

WHERE table1.fld1<>table2.fld1 OR table1.fld1<>table2.fld2 OR ....

to cut down on the typing, alias your tables as A and B

[orange]

@mtspeer

And I feel like I will get an error saying the query is too complex.

Did you try anything? If so, what exactly (please show us the query sql). And did you get a message?

[knarfreppep]

Might something like the code below work?

After it is run details of mismatches would be in tblMismatches.

Public Sub gsubCompareFields()

Dim rs1 As Recordset
Dim intField As Integer
Dim rs2 As Recordset

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tblMismatches"

Set rs1 = CodeDb.OpenRecordset("tblA", dbOpenSnapshot)
While Not rs1.EOF
Set rs2 = CodeDb.OpenRecordset("SELECT * FROM tblB WHERE lngRecordID = " & rs1!lngRecordID, dbOpenSnapshot)
For intField = 1 To 25
If NOT rs1.Fields(intField) = rs2.Fields(intField) Then
DoCmd.RunSQL "INSERT INTO tblMismatches ( lngRecordID, intField ) SELECT " & rs1!lngRecordID & ", " & intField
End If
Next
For intField = 51 To 125
If NOT rs1.Fields(intField) = rs2.Fields(intField) Then
DoCmd.RunSQL "INSERT INTO tblMismatches ( lngRecordID, intField ) SELECT " & rs1!lngRecordID & ", " & intField
End If
Next
rs1.MoveNext
Wend
DoCmd.SetWarnings True

Set rs2 = Nothing
Set rs1 = Nothing

End Sub

[MTSPEER]

Well I didnt know there could only be 16 joins. And can't there only be like 99 fields in one query or something like that? SO I figured it would give me a "query is too complex" message if I tried to Left join all the fields then give the fields in Table2 Is Null. So Im just going to make a lot of queries

[CJ_London]

And can't there only be like 99 fields in one query or something like that?

the number is 255

[MTSPEER]

There is a limit of 99 AND operators in WHERE clauses.

[orange]

If the fields in the 2 tables have exactly the same names, and there are 200 fields in each, you could use recordsets. (Could be slower to execute, but the logic would be clear)

for example (general approach)

Declare variables/objects
Using tblA and tblB
Open tblA and tblB
Do while    tblA or tblB are not EOF
   For i = 0 to 199
     if tblA.fields(i) <> tblB.fields(i) Then
     record the  mismatch
     end if
   Next i
Loop

Just typing while thinking of the issue.

[knarfreppep]

[QUOTE=orange;283660] ... (general approach)

Did you miss my earlier post?

[orange]

No, I just have no idea why you arbitrarily chose 1- 50, then 51 -125.

I don't think the OP has a good grasp of what he is trying to accomplish. Rarely have I seen posters who don't try something for fear it may result in a message???

He/she said they had 200 fields, then said they only had to check 100???

We need the OP to describe the issue in clear, simple English -Starting with why you have 2 tables with exactly the same format/design.

[knarfreppep]

Hmmm ... I suspect I have a reasonable grasp on what MTSPEEP seeks to accomplish ... I certainly wouldn't put him/her down for the way he/she described the 'problem' ... I chose two ranges to give an example of how to deal with non-contiguous fields among those to be checked.

[orange]

My intent was not to put the OP down or up. I was trying to say/show that the issue described in the post was vague and unclear. There was no background on the 2 tables; the 200 fields etc.
200 fields in a relational table is an extremely rare case. Perhaps the OP has such a case, and if so, I encourage him/her to describe same so we can all learn.

Thanks for the update on the non-contiguous fields -makes sense now.

[knarfreppep]

Most of the requirements people have of me are vague and unclear ... one struggles on.