Selecting matching records from two tables simultaneously

[icewiper]

Hello, here is the question. I have two tables, a tempProperty and a Property table. The TempProperty table gets created while being imported from an excel sheet. The TempProperty table will hold the current data and it needs to be compared to the old data. The fields on them will be the same. What I am attempting to do run two record sets to pull the information from both to match simultaneously and to compare the data while running my code. I know this can be done with an update query, but I have a lot more I want to do with this if I can get it to work. Any help would be greatly appreciated.
I will post the starter code I have below. Oh, and I apologize if I don't respond back right away, I'm 8 hours ahead of the U.S.

Dim TempGPNbr, TempAssignedEmp, TempRecDate, TempSiteID, TempTransFromSite, TempStatus, TempComments, TempShops As String
Dim PropGPNbr, PropAssignedEmp, PropRecDate, PropSiteID, PropTransFromSite, PropStatus, PropComments, PropShops As String
Dim rs, rs2 As Object
Dim ddbase, ddbase2 As Database

str = "SELECT tbl_PropertyImport.GPNbr, tbl_PropertyImport.SubCategory, tbl_PropertyImport.Description,"
str = str & " tbl_PropertyImport.UI, tbl_PropertyImport.SN, tbl_PropertyImport.AssignedPerson,"
str = str & " tbl_PropertyImport.RecDate, tbl_PropertyImport.TurnInDate, tbl_PropertyImport.SiteID,"
str = str & " tbl_PropertyImport.TransferFromSite, tbl_PropertyImport.CatID, tbl_PropertyImport.Comments,"
str = str & " tbl_PropertyImport.Status, tbl_PropertyImport.Shops"
str = str & " FROM tblProperty RIGHT JOIN tbl_PropertyImport ON tblProperty.GPNbr = tbl_PropertyImport.GPNbr"
str = str & " WHERE (((tbl_PropertyImport.GPNbr)=[tblProperty].[GPNbr]));"

str2 = "SELECT tblProperty.GPNbr, tblProperty.SubCategory, tblProperty.Description, tblProperty.UI,"
str2 = str2 & " tblProperty.SN, tblProperty.AssignedPerson, tblProperty.RecDate, tblProperty.TurnInDate,"
str2 = str2 & " tblProperty.SiteID, tblProperty.TransferFromSite, tblProperty.CatID, tblProperty.Comments,"
str2 = str2 & " tblProperty.Status, tblProperty.Shops"
str2 = str2 & " FROM tblProperty"

Set ddbase = CurrentDb
Set rs = ddbase.OpenRecordset(str, dbOpenSnapshot)
''rs.MoveLast
''rs.MoveFirst

Set ddbase2 = CurrentDb
Set rs2 = ddbase2.OpenRecordset(str2, dbOpenSnapshot)
''rs2.MoveLast
''rs2.MoveFirst

TempGPNbr = rs(0)
TempAssignedEmp = rs(5)
TempRecDate = rs(6)
TempSiteID = rs(8)
TempTransFromSite = rs(9)
TempComments = rs(11)
TempStatus = rs(12)
TempShops = rs(13)

PropGPNbr = rs2(0)
PropAssignedEmp = rs2(5)
PropRecDate = rs2(6)
PropSiteID = rs2(8)
PropTransFromSite = rs2(9)
PropComments = rs2(11)
PropStatus = rs2(12)
PropShops = rs2(13)

example:
If rs(0) = rs2(0) And rs(5) = rs2(5) And rs(6) = rs2(6) And rs(8) = rs2(8) And rs(9) = rs2(9) And rs(12) = rs2(12) And rs(11) <> rs2(11) Or rs(13) <> rs2(13) Then
MsgBox "gotOne"
End If

 

[fredericofonseca]

select ....
from tbl1
inner join tbl2
on
tbl1.field1 = tbl2.field1
and tbl1.field2 = tbl2.field2

and so on.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[icewiper]

Hey there fred, thanks for the response. I used the select statement a few different ways and still ended up with the same results i did before with the unique identifiers not matching while running through the recordset. But at least it will give me something to start with.
Thanks again

 

[Andrzejek]

Dim [b]TempGPNbr, TempAssignedEmp, TempRecDate, TempSiteID, TempTransFromSite, TempStatus, TempComments,[/b] TempShops As String
Dim [b]PropGPNbr, PropAssignedEmp, PropRecDate, PropSiteID, PropTransFromSite, PropStatus, PropComments,[/b] PropShops As String
Dim [b]rs,[/b] rs2 As Object
Dim [b]ddbase,[/b] ddbase2 As Database

Of course you do realize that all bold variables in your Dim statements are Variants.

---- Andy