Problem comparing 2 tables 1

[suziqueue]

I have 2 tables that I compare order number, item, qty, and finish codes everyday. Both tables are being extracted out of two separate systems. One table has both order number and line number as key fields, the other has no key field at all (and unfortunately cannot be changed due to system limitations) My problem is when comparing finish codes I may have the same order number, part, and qty listed twice in both tables with the only difference being the finish code (one may be sahara the other satin black). My report will show these lines as an exception (when it really isn't). For example Order 09080 Part SUS-414 QTY 4 Finish Code in Table1 SA Finish Code in Table2 SB, the next line will read Order 09080 Part SUS-414 QTY 4 Finish Code in Table1 SB Finish code in Table2 SA. I know it should be very simple to loop through the records in table1 until it finds it's match in table2 then continue looping until it finds a record that does not match in table2, but I do not have a clue how to accomplish this. Needless to say I am VBA illiterate (close enough anyway). If this is a duplicate post I apologize, but could someone please lend a hand. I have looked high and low trying to find an example that I could modify for my use - either I am not looking in the right place (highly possible) or am using the wrong keywords (also highly possible)

Any assistance would be greatly appreciated!!!

TIA, RLW (Brain dead on a Monday)

 

[MichaelRed]

Sorry, I am somewhat lost on this. It 'SEEMS" like you just want the standard old unmatched query - but on FOUR fields instead of one. You really shouldn't need any (VBA) code for this, just generate the unmatched query and "GO".

You may want to generate the initial query with only two fields and then select the "Modify thte query" option, just to SEE how the thing is set up, and add the other fields, field relationships and criteria. It might help you to understand the process.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[suziqueue]

It is rather confusing - I am running an unmatched query with links between the 2 tables on Order Number, Part Number, and Qty. The criteria in the Finish Code field for table1 is &quot;<> table2!finishcode&quot;. If both tables had line numbers for each line of the order I would not have a problem. Running the query as it is simply goes out and looks at Table2 to see if it does not match if the first instance it comes to for that order number, part number and qty doesn't match it does not look to see if perhaps a match does exist for the same order number, part number and qty. It is possible to have multiple matches for order number, part number and qty - but each match will have a different finish code. What I am looking for is line items on orders that have not been maintained properly in either table. Perhaps changed in one but not the other or imported incorrectly. I hope this clarifies my situation.
TIA, RLW

 

[MichaelRed]

JUST modify the unmatched query to have it Join on all four fields and check for reach of hte 'table2' values to be NULL. IT IS JUST an elaborated &quot;UnMatched&quot; query. Works. Try IT!

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[suziqueue]

Thanks Michael,
It is still not returning what I need though. I'll play around with it I'm sure I'll figure it out.

RLW

 

[MichaelRed]

Check wheather your criter are 'ANDED' or 'ORED'. They (should be ORED - each on a seperate criteria line) so that ANY difference makes it included.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[WHM]

SQL querries are powerful tools, and with enough research and testing they seem capable of doing just about anything, but there are times when writing code is just plain easier. Try something along these lines for a sloppy, but easy to develop solution. If the recordset counts are less than a couple of thousand this should not be too slow.

Dim rs1 as Recordset
Dim rs2 as Recordset
Dim SQL1 as String
Dim SQL2 as String
Dim boolSame as Boolean

'Create SQL statements to generate the appropriate records, ie a list of order numbers from computer 1 and a list of order numbers from computer 2

Set rs1=db.openrecordset SQL1
Set rs2=db.openrecordset SQL2
'Note: this is DAO Syntax, you can use other connection metods, just replace the DAO syntax with whatever you decide to use.
rs1.movefirst
rs2.movefirst
Do
do
if rs1!Field1=rs2!Field1 then
if rs1!Field2=rs2!Field2 then
if rs1!Field3=rs2!Field3 then
if rs1!Field4=rs2!Field4 then
boolSame=True
else
boolSame=False
End if
else
boolSame=False
End if
else
boolSame=False
End if
else
boolSame=False
End if

If boolsame=true then
'Some handling routine if both records are the same
end if
rs2.movenext
loop until rs2.eof
rs2.movefirst
rs1.movenext
loop until rs1.eof

set rs1=nothing
set rs2=nothing


Yes you can combine the nested ifs into Anded ifs, and you could set boolSame = False at the beginning of each iteration thus removing the need for the Else Clauses, I did it this way for clarity.

It is ugly and slow, but fool proof and easy.

Hunter

 

[suziqueue]

Michael,
I have tried changing my field joins every which way immaginable, changing my criteria every which way imaginable (including your suggestion) but have yet to successfully return only the records that are truly unmatched. I do appreciate your suggestions, thank you for your help.

RLW

 

[suziqueue]

Hunter,
Thank You so much! I will probably add this as an extra step so it will only have to query around 500 - 600 records (Otherwise we're talking 15000)I'll give it a shot and let you know what happens.

Thanks Again,
RLW