Looking for faster method

  • Thread starter Thread starter Tom & Roxanne Hanson
  • Start date Start date
T

Tom & Roxanne Hanson

Greetings all.

I have two tables that have a many to many relationship for 3 fields. Table
1 has 6 fields, table 2 has 12. Each table has 1 unique field (but not
related to each other). I need a method to select a record from the first
table, find a record in the second table that matches the 3 common fields
and then update each record. I need to loop through all records in both
tables to mark each as a match. To add to the dilema, after each record
from table 1 has been matched it cannot be reused, the same for table 2.

The method I designed was to pull all records into 2 RECORDSETS, retreive
the field data from recordset1 record 1, verify it was not marked as
matched, check it against each record in recordset 2, if a match (and not
marked as a match) then run an update query to each record showing a match
found. I then reset recordset 2, loop to record 2 of recordset 1 and do it
again. Since my tables have up to 20,000 records this is very time
consuming and resource crazy.

If anyone has any ideas, please help.

Tom Hanson
 
Greetings all.

I have two tables that have a many to many relationship for 3 fields. Table
1 has 6 fields, table 2 has 12. Each table has 1 unique field (but not
related to each other). I need a method to select a record from the first
table, find a record in the second table that matches the 3 common fields
and then update each record. I need to loop through all records in both
tables to mark each as a match. To add to the dilema, after each record
from table 1 has been matched it cannot be reused, the same for table 2.

The method I designed was to pull all records into 2 RECORDSETS, retreive
the field data from recordset1 record 1, verify it was not marked as
matched, check it against each record in recordset 2, if a match (and not
marked as a match) then run an update query to each record showing a match
found. I then reset recordset 2, loop to record 2 of recordset 1 and do it
again. Since my tables have up to 20,000 records this is very time
consuming and resource crazy.

If anyone has any ideas, please help.

Tom Hanson
Click to expand...

A single Update query would be MUCH simpler and probably much faster.
Create an Index on the combination of the three fields in the table
which will be updated. Create a Query joining the two tables on the
three fields; apply a criterion to limit the records to those not
marked, and update all of them in one go.

This may run into a problem if the three fields don't uniquely
identify a record - which record would you want to mark if one record
in Table2 matches three or four records in Table1?
 
The data is not unique.
Example:
Table 1 has 4 records with field 1 of A, field 2 of b, field 3 of c
Table 2 has 3 records that match
The program needs to mark the first 3 in table 1 and all 3 in table 2 as
matched. The 4th record in table 1 should be unmatched. The variance could
be the other way as well, 3 records in table 1 and 4 in 2 then the last
record in 2 would be unmatched.

Clear as mud?
 
The data is not unique.
Example:
Table 1 has 4 records with field 1 of A, field 2 of b, field 3 of c
Table 2 has 3 records that match
The program needs to mark the first 3 in table 1 and all 3 in table 2 as
matched. The 4th record in table 1 should be unmatched. The variance could
be the other way as well, 3 records in table 1 and 4 in 2 then the last
record in 2 would be unmatched.

Clear as mud?
Click to expand...

Pretty muddy to me still! One big concern is that you say "the first
three records" and "the fourth record". Ain't no such animal! Tables
have no order, they're unordered "bags" of data. Is there any other
field which could provide a sequence? Even if there is this may be
tricky enough that your recordset and repeated updates may be the only
way to go!
 
Back
Top