comparing a record in one table against a record in another using VBA

charliej2001

This is my first post on groups and id like to say that already its
helped me out loads, but can't quite find what im looking for now

I have 3 tables storing information about people; Main, Temp and Dupl.
What I want to do, using VBA, is check each record in the Temp table
(10-50 records) against each record in the Main table (1000+ records);
if any records then match, I want to store them in the Dupl table. The
3 tables are identical in the info they store, and they have the same 3
primary keys: First Name, Last Name, Job

I'm about to stop for the day (UK time) and have a think about how to
efficiently perform the check, but as I'm new to using VB I don't know
enough of the language to be able to perform my checks without using
loops and loops of code. I really dont want to do this as it needs to
be as fast as possible.

Any ideas peeps?

Thanks in advance

Charlie

Jul 19 '06 #1

ManningFan

Does it have to be VBA? I would think a query would be quicker and
easier, although I guess you can pass the SQL via VBA and then you can
change the parameters if you like.

charliej2...@googlemail.com wrote:

This is my first post on groups and id like to say that already its
helped me out loads, but can't quite find what im looking for now

I have 3 tables storing information about people; Main, Temp and Dupl.
What I want to do, using VBA, is check each record in the Temp table
(10-50 records) against each record in the Main table (1000+ records);
if any records then match, I want to store them in the Dupl table. The
3 tables are identical in the info they store, and they have the same 3
primary keys: First Name, Last Name, Job

I'm about to stop for the day (UK time) and have a think about how to
efficiently perform the check, but as I'm new to using VB I don't know
enough of the language to be able to perform my checks without using
loops and loops of code. I really dont want to do this as it needs to
be as fast as possible.

Any ideas peeps?

Thanks in advance

Charlie

Jul 19 '06 #2

pietlinden

Any reason why the find Unmatched query wizard wouldn't work?

Jul 19 '06 #3

charliej2001

I'd like it to be VBA just for the fact that I'm learning the language,
so it's nice to pick up tips and tricks to help do things faster.

@pietlinden - Using the unmatched query finds records in one table that
aren't in the other table. What I want to do is find records in one
table that are in the other table, then make a copy of these records
into a third table. If you can do this with the unmatched query, a
point in the right direction would be great, as I've only just started
to use Access (as well as VBA) so still learning how to do all the
things I want to do

Cheers

Charlie

ManningFan wrote:

Does it have to be VBA? I would think a query would be quicker and
easier, although I guess you can pass the SQL via VBA and then you can
change the parameters if you like.

charliej2...@googlemail.com wrote:

This is my first post on groups and id like to say that already its
helped me out loads, but can't quite find what im looking for now

I have 3 tables storing information about people; Main, Temp and Dupl.
What I want to do, using VBA, is check each record in the Temp table
(10-50 records) against each record in the Main table (1000+ records);
if any records then match, I want to store them in the Dupl table. The
3 tables are identical in the info they store, and they have the same 3
primary keys: First Name, Last Name, Job

I'm about to stop for the day (UK time) and have a think about how to
efficiently perform the check, but as I'm new to using VB I don't know
enough of the language to be able to perform my checks without using
loops and loops of code. I really dont want to do this as it needs to
be as fast as possible.

Any ideas peeps?

Thanks in advance

Charlie

Jul 20 '06 #4

charliej2001

Ok, well I found an easy way of what I wanted to do by using 2 append
queries.

The first query copies everything in Temp to Dupl on the condition that
the primary keys match (criteria: is not null), then the 2nd query
copies everything in Temp to Main on the condition that the primary
keys don't match (criteria: is null).

Then I've called these queries using VBA

Thanks for both of your replies

Charlie
ch**********@googlemail.com wrote:

I'd like it to be VBA just for the fact that I'm learning the language,
so it's nice to pick up tips and tricks to help do things faster.

@pietlinden - Using the unmatched query finds records in one table that
aren't in the other table. What I want to do is find records in one
table that are in the other table, then make a copy of these records
into a third table. If you can do this with the unmatched query, a
point in the right direction would be great, as I've only just started
to use Access (as well as VBA) so still learning how to do all the
things I want to do

Cheers

Charlie

ManningFan wrote:

Does it have to be VBA? I would think a query would be quicker and
easier, although I guess you can pass the SQL via VBA and then you can
change the parameters if you like.

charliej2...@googlemail.com wrote:

This is my first post on groups and id like to say that already its
helped me out loads, but can't quite find what im looking for now
>
I have 3 tables storing information about people; Main, Temp and Dupl.
What I want to do, using VBA, is check each record in the Temp table
(10-50 records) against each record in the Main table (1000+ records);
if any records then match, I want to store them in the Dupl table. The
3 tables are identical in the info they store, and they have the same 3
primary keys: First Name, Last Name, Job
>
I'm about to stop for the day (UK time) and have a think about how to
efficiently perform the check, but as I'm new to using VB I don't know
enough of the language to be able to perform my checks without using
loops and loops of code. I really dont want to do this as it needs to
be as fast as possible.
>
Any ideas peeps?
>
Thanks in advance
>
Charlie

Jul 20 '06 #5