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


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


charliej2001@xxxxxxxxxxxxxx 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...@xxxxxxxxxxxxxx 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

.

Relevant Pages

  • Re: How to use a VBA query results in non-VBA query?
    ... temper table which it built via vba then queries are run against the temp ... If you go down that route consider multi-user problems with the temp ... >I need the results of a query in several other queries. ...
    (microsoft.public.access.queries)
  • Re: Informix vs Oracle vs DB2. SQL Query optimization.
    ... but I think you're confused because you don't know Oracle. ... the query chooses to limit TAB_A by the col_id. ... initially we have the collection as geo1. ... case of 270,000 rows in the collection, and 60,000 rows in the temp ...
    (comp.databases.informix)
  • Re: Relationships, back end
    ... query the same, using the linked file directly rather than a temp table. ... |> into tblMovements but into a temp table, ... | adds the EquipmentID field the the temp table". ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Truncation Issue w/ Strings
    ... I was getting the results from an access query, so I ended up having to ... create a temp table, and insert the rows into a memo field in the temp ... "fdhsjak hfdjksh fdshfjkdlsh fjdshfj hdslfh dshfjlshjfhdsjflhdsjlfh ... dsjlkhfjdshfjdha jfdhj f " & _ ...
    (microsoft.public.vb.general.discussion)
  • Re: Writing a query using VBA - Variable where clause
    ... that collection and set its member to true for member items IN the "temp" ... 2- Make an ad hoc query from a string. ...
    (microsoft.public.access.queries)