By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
Any reason why the find Unmatched query wizard wouldn't work?

Jul 19 '06 #3

P: n/a

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

P: n/a

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

This discussion thread is closed

Replies have been disabled for this discussion.