Efficient Comparison of Multiple Fields on Different Tables

[joeyT]

Hello,

I am wondering if there is an efficient way to compare two fields from one table to another two fields from another table. So basically

If targetTable.Field1.Value =  sourceTable.Field1.Value And targetTable.Field2.Value =  sourceTable.Field2.Value Then
targetTable.Field3.Value = sourceTable.Field3.Value

The problem is that I need to run this for all entries in targetTable. The only I could think of was to use 2 nested for loops (one for target table and one for source table) as outlined in the following (my data is currently in Excel, but I want to import it to Access)

For i = 2 To 5754
        For j = 2 To 3500
            If targetSheet.Range("I" & i).Value = sourceSheet.Range("AR" & j).Value And targetSheet.Range("K" & i).Value = sourceSheet.Range("AS" & j).Value Then
                targetSheet.Range("I" & i).Value = sourceSheet.Range("AT" & j).Value

The above code works but it is really slow (takes about 12 mins on a high-end CPU).

Is there a simpler more efficient way to do this? Thanks in advance

 

[redalert]

Databases work differently to spreadsheets and this job would certainly be easier in the database.

Do you want to end up with the data in the Excel or Access?

Import the data into Access and make sure that the field types and sizes are correct.

Create a query using the two tables and join them on targetTable.Field1 = sourceTable.Field1.Value And targetTable.Field2 = sourceTable.Field2

The result will be the records where both conditions are met and the only ones that you want to update.

Change the query to an update query and update targetTable.Field3 with the value in sourceTable.Field3.

 

[spikepl]

Impor your tables into Access. Play with the matched-records query wizard. When happy, change query to update query. If you do not know what any of this means then get cracking anyway and do it one step at a time, and it will eventually come to you.

 

[joeyT]

Databases work differently to spreadsheets and this job would certainly be easier in the database.

Do you want to end up with the data in the Excel or Access?

Import the data into Access and make sure that the field types and sizes are correct.

Create a query using the two tables and join them on targetTable.Field1 = sourceTable.Field1.Value And targetTable.Field2 = sourceTable.Field2

The result will be the records where both conditions are met and the only ones that you want to update.

Change the query to an update query and update targetTable.Field3 with the value in sourceTable.Field3.

Thanks for your response. No I want the data to end up in Access. I guess I will have to enter these in the SQL view of the query if I am not wrong

 

[redalert]

Set the query up in the Query Design Grid, it will be easier.