Comparing data between two tables

[GLT]

Hi,

I have a table where I import data to a table. This data contains a list of
Services (some which are valid, and some which can be ignored).

I have another permanent table that contains a list of Services which can be
ignored.

What I would like to do, is after the import data has completed, compare the
two tables and flag all matched records (in the first table) as ignore (i
already have a check box field in this table for this purpose).

Would an update query be best for this, or cloning a record set in VBA and
scanning through all records in the table be better?

Any assistance is always greatly appreciated...

Cheers,
GLT.

 

[Marshall Barton]

I have a table where I import data to a table. This data contains a list of
Services (some which are valid, and some which can be ignored).

I have another permanent table that contains a list of Services which can be
ignored.

What I would like to do, is after the import data has completed, compare the
two tables and flag all matched records (in the first table) as ignore (i
already have a check box field in this table for this purpose).

Would an update query be best for this, or cloning a record set in VBA and
scanning through all records in the table be better?

Use an update query. It is an unusual situation where using
a recordset loop is faster.

 

[Tom van Stiphout]

On Mon, 1 Feb 2010 00:31:01 -0800, GLT <[email protected]>
wrote:

If you can write it in SQL, it's going to be faster than doing the
same with VBA.
update myTable set myYesNoField = True
where myServiceField in (select myServiceField from
myTableWithServicesToBeIgnored)

-Tom.
Microsoft Access MVP

 

[John Spencer]

An update query would be most efficient.

UPDATE ImportTable LEFT JOIN PermanentTable
ON ImportTable.ServiceID = PermanentTable.ServiceID
SET ImportTable.FlagField = [PermanentTable].[ServiceID] is not Null

I would probably not bother to do this unless it was important for performance
reasons. I would use a query and left join the Permanent table to import
table and use that to identify which records should be ignored. WHY? If you
add or delete records in the Permanent table then you have to make sure you
run the update query again so that the FlagField (Ignore) is correctly set.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County