Compare 2 tables

[yoonie_85]

hello

i'm in desperate need of some help, as it is urgent...i have taken a lot of different approaches to this problem, but nothing seems to be working.

here's what i'm trying to do...

I am trying to write a module in access in which I am comparing two tables.

tabel1 has 2 id numbers in the first 2 fields, and a phone number in the 3rd field
table 2 has 2 id numbers, and 3 emtpy fields for phone number, Yes column and No Column.
I have to take each pair of ID numbers fromthe 1st table and check to see if the same pair is in the second table. If so, I want to add the phone number to the 2nd table and inclue yes or no in the corresponding column.

any help would be greatly appreciated. Thank you.

 

[Rob.Mills]

You need to create an update query that joins those two tables on both fields.

 

[Surjer]

A quick and dirty way would be to write an update query and then call it from code.

DoCmd.OpenQuery ("MyUpdateQuery")

If you need help with the query post back...

Hope that helps...

 

[yoonie_85]

Hey, thanks so much for the quick reply

i've been trying to write a module in VBA...and so far i've come up with this:

Sub Create()

Dim OriginalRS As Recordset
Dim DataRS As Recordset
Dim CustCode As Long
Dim PremCode As Long
Dim Cust2Code As Long
Dim Prem2Code As Long

Dim I, J, a As Integer
Dim TheFile As String



Set DataRS = CurrentDb.OpenRecordset("Data", dbOpenForwardOnly)
Set OriginalRS = CurrentDb.OpenRecordset("Original", dbOpenDynaset)
CustCode = DataRS!CUST_CODE
PremCode = DataRS!PREM_CODE
Cust2Code = OriginalRS!CustCode
Prem2Code = OriginalRS!PremCode

Do Until DataRS.EOF

Do Until CustCode = Cust2Code And PremCode = Prem2Code
OriginalRS.MoveNext
Cust2Code = OriginalRS!CustCode
Prem2Code = OriginalRS!PremCode
Loop

'add the phonenumbers, and yes or no statements in the proper fields...which I do not know how to do
Loop
DataRS.MoveNext

DataRS.Close
Set DataRS = Nothing
OriginalRS.Close
Set OriginalRS = Nothing

End Sub

i have a feeling i'm not doing ir proeprly at all though....
thanks

 

[Rob.Mills]

That was a good attempt and certainly you can go about it that way. But the simplest way is using the update query and calling it the way Surjer suggested. I find the simplest way is best. Less chance of errors and more efficient.

 

[yoonie_85]

I would to it that way....but I dont understand how.

 

[Surjer]

- I think this might work???

Sub Create()

Dim OriginalRS As Recordset
Dim DataRS As Recordset

Set DataRS = CurrentDb.OpenRecordset("Select * from [Data]")

Do While Not DataRS.EOF
Set OriginalRS = CurrentDb.OpenRecordset("Select * from Original where OriginalRS!CustCode = " & DataRS("CUST_CODE") & " and OriginalRS!PremCode = " & DataRS("PREM_CODE")

If not OriginalRS.RecordCount = 0 Then

DataRS.Edit
DataRS("MyPhoneNumberField").Value = OriginalRS("MyPhoneNumberField").Value
DataRS("YesNoField").Value = "YES"
DataRS.Update
DataRS.MoveNext
Loop

DataRS.Close
Set DataRS = Nothing
OriginalRS.Close
Set OriginalRS = Nothing

End Sub

 

[yoonie_85]

Hello,

I have tried combining ur code and my code together to get this....

Sub Create()

Dim OriginalRS As Recordset
Dim DataRS As Recordset
Dim CustCode As Long
Dim PremCode As Long
Dim Cust2Code As Long
Dim Prem2Code As Long

Dim I, J, a As Integer
Dim TheFile As String



Set DataRS = CurrentDb.OpenRecordset("Data", dbOpenForwardOnly)
Set OriginalRS = CurrentDb.OpenRecordset("Original", dbOpenDynaset)
CustCode = DataRS!CUST_CODE
PremCode = DataRS!PREM_CODE
Cust2Code = OriginalRS!CustCode
Prem2Code = OriginalRS!PremCode

Do Until DataRS.EOF

Do Until CustCode = Cust2Code And PremCode = Prem2Code
OriginalRS.MoveNext
Cust2Code = OriginalRS!CustCode
Prem2Code = OriginalRS!PremCode
Loop
OriginalRS.Edit
OriginalRS("PhonNumber").Value = DataRS("NumDays").Value
OriginalRS("No_WH").Value = "YES"
OriginalRS.Update


'add the phonenumbers, and yes or no statements in the proper fields...which I do not know how to do
Loop
DataRS.MoveNext

DataRS.Close
Set DataRS = Nothing
OriginalRS.Close
Set OriginalRS = Nothing

End Sub

but, im wondering i have 650000 records to go through, so it's taking forever for this moduel to run. is there a faster way?

 

[Surjer]

Using recordsets is about the slowest way to do it. I would still use an update query. and call it with code. Just search the queries section and there is TONS of samples you will find as far as syntax of the query...

 

[yoonie_85]

I"m really sorry, i'm a beginner when it comes to this stuff...

does this look right in any way?

UPDATE Original
SET WH = CASE
WHEN EXISTS (SELECT Original.PremCode FROM Original, Data
WHERE Original.PremCode = Data.PREM_CODE AND
Original.CustCode = Data.CUST_CODE)
THEN 'Y'
END,

WH_Rental = CASE
WHEN EXISTS (SELECT Original.PremCode FROM Original, Data
WHERE Original.PremCode = Data.PREM_CODE AND
Original.CustCode = Data.CUST_CODE)
THEN 'N'
END,

WH_Constant = CASE
WHEN EXISTS (SELECT Original.PremCode FROM Original, Data
WHERE Original.PremCode = Data.PREM_CODE AND
Original.CustCode = Data.CUST_CODE)
THEN 'N'
END