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

[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.

[e-mail] 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

[pietlinden]

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

[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.

[e-mail] 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

[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.

[e-mail] 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