Comparing records between tables in Access using loop

[guzzo]

I am a beginner VBA user. I need to compare two tables in Access (97) and display results in a column in each table.
The 'reschedule' table has the following fields: Item, NeededQty, NeededDate, count. The 'po' table has the following fields: Item, PO, Qty, DueDate, count1. I need to be able to count between the two tables using the item as the link. I'm looking for an end result that looks like this:
Table A Table B
Reschedules PO's
Item Count Item1 Count1
1234 1 1234 2
1234 3 1234 4
1234 5 1244 7
1244 6 1244 9
1244 8 1255 11
1255 10 1266 13
1266 12 1277 16
1266 14
1277 15

'where it finds matching items it puts a count - if there's more PO lines than reschedule lines it leave the count blank. There can be more PO's than reschedule but never more reschedules than PO's.


I have been trying to use the following code :

Function Test11a()

Dim count As Integer
Dim count2 As Integer
Dim db As Database
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim strMsg As String
Dim varReturn As Integer
Dim Item1 As String
Dim Item2 As String
count = 0


Set db = DBEngine(0)(0)
Set rs1 = db.OpenRecordset("Net Reqs To Reschedule", dbOpenDynaset)
Set rs2 = db.OpenRecordset("Open PO's", dbOpenDynaset)


rs1.MoveFirst
rs2.MoveFirst
strMsg = "Counting Records " & "..."
varReturn = SysCmd(acSysCmdInitMeter, strMsg, rs1.RecordCount)


Do While Not rs1.EOF 'Outer Loop
Item1 = rs1("Item&quot

count = count + 1
varReturn = SysCmd(acSysCmdUpdateMeter, count)

rs1.Edit
rs1("Count&quot = count
rs1.Update



Do Until Item2 > Item1 'Inner Loop
Item2 = rs2("Item&quot

strMsg = "Counting PO " & "..."
varReturn = SysCmd(acSysCmdInitMeter, strMsg, rs1.RecordCount)


If Item1 = Item2 Then

count = count + 1
varReturn = SysCmd(acSysCmdUpdateMeter, count2)
rs2.Edit
rs2("Count2&quot = count
rs2.Update
rs2.MoveNext
End If


Loop
rs1.MoveNext

Loop


varReturn = SysCmd(acSysCmdRemoveMeter)
Exit Function
End Function

 

[Sawatzky]

dear guzzo,


perhaps I am missing something but couldn't you just use 3 queries ?
1 query for each table doing the count and a third query joining them together?

then you could retrieve the result in a record, if needed?

regards Astrid

 

[guzzo]

I have to be able to link one record in the reschedule table to one record in the po table while decrementing the qty. If the first reschedule record states Qty Needed: 100 and the first po record states Qty: 500 and the second reschedule record states Qty Needed: 400 - I don't need to find another matching po record because the first po record covered both reschedule records.
I don't have that math in my code yet because as of this point I can't even get the code to move between the two tables. Once I can get the code to accurately match the two tables then I'll add the math to decrement the qty's as needed.