MS Access record set statement
Hello Experts,
Can you please tell me how to revise the statement below so I can make it match on 3 or 4 fields?
The current statement is:
rs.FindFirst field1= & rs2!field1
Basically I'm trying to compare two tables and there isn't a primary field to identify the record, so I need to combine multiple fields to make a primary field.
Of course, the easiest way to do this would be to use Office 365 and just dump the tables into Excel and do a compare, but I only have Office 2007 so I thought I'd just run a subroutine.
Thank you.
Steph
Can you please tell me how to revise the statement below so I can make it match on 3 or 4 fields?
The current statement is:
rs.FindFirst field1= & rs2!field1
Basically I'm trying to compare two tables and there isn't a primary field to identify the record, so I need to combine multiple fields to make a primary field.
Of course, the easiest way to do this would be to use Office 365 and just dump the tables into Excel and do a compare, but I only have Office 2007 so I thought I'd just run a subroutine.
Thank you.
Steph
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually, the easiest way is to build a query. You can join on multiple columns. It will be more efficient than using find to move around the recordset. Don't forget that FindFirst always goes back to the beginning before searching again. Of course, if the recordset is only a few hundred rows, it won't matter much but once you get into the thousands, you will see a difference.
Or, if you are a programmer, you could sort the two recordsets and then do a two file match.
Or, if you are a programmer, you could sort the two recordsets and then do a two file match.
Can you please tell me how to revise the statement below so I can make it match on 3 or 4 fields?
All sorts of things suggest themselves, but you're a little sparse on detail.
and there isn't a primary field to identify the record, so I need to combine multiple fields to make a primary field.
Access is thoroughly capable of a multi-column primary key, but I don't think that does you any good -- although I could be wrong, I always use auto-numbers for PKs.
@PatHartman is right about the query, too. You can create a query that has multiple JOINs between two tables. Just because you normally only see one doesn't mean you can't drag-and-create more. That can be really handy as JOINs are more efficient that WHERE clauses in SQL.
Sample attached
multiple-JOINS.mdb
All sorts of things suggest themselves, but you're a little sparse on detail.
and there isn't a primary field to identify the record, so I need to combine multiple fields to make a primary field.
Access is thoroughly capable of a multi-column primary key, but I don't think that does you any good -- although I could be wrong, I always use auto-numbers for PKs.
@PatHartman is right about the query, too. You can create a query that has multiple JOINs between two tables. Just because you normally only see one doesn't mean you can't drag-and-create more. That can be really handy as JOINs are more efficient that WHERE clauses in SQL.
Sample attached
multiple-JOINS.mdb
I also normally use autnumbers as primary keys. HOWEVER, to implement business rules of uniqueness, you MUST define multi-field unique indexes. Most Access apps do not have data sets large enough to worry about the "efficiency" of single field surrogate keys vs multi-field natural keys so don't even worry about that. Just use the autonumber PK and make a separate unique index for your business rules. The single field PK is much more convenient when building queries but the thing that convinced me is when I tried to create a usable combobox on a multi-field PK. It can't be done due to the way combos work.
ASKER
This works perfect what I need to do. I need to run the results through some if statements after it matches so this is the most efficient way for this task.
Thank you.
Stephanie
Thank you.
Stephanie
Actually, queries are significantly more efficient than code loops with "findfirst".
rs.FindFirst field1 = "some value" AND field2 = "some value"
Jim.