access 2 table update query

Question

I have a project to bring data from an excel spreadsheet and use it to update values in existing fields in an Access table. My programming skills are very dated so although I know the logic I don’t know how to do it with VBA. I built another table in the DB for the excel data and wrote a query to compare the two tables and the two fields that should be the same. When the fields match than I will update some fields in the existing table for the imported table.  When I run the query I get 32 matches when it should be 1000.

Here is the logic of what I need it to do:

Read sequentially the imported table (TBL-A) then from the beginning read the older table (TBL-B) comparing two different fields.  When there is a match, update the fields in TBL-B.

Read the next record in TBL-A and read, from the beginning, TBL-B looking for a match.

I am establishing a relationship with the two table in the query using option 1. The fields that I am comparing are short text and none are indexed.

I know my logic is probably as dated as my programming skills but I know what I am trying achieve. After all of the updates I will delete the imported table.

Thank you!

Answer 1

Ken,

After looking at it closer I may have spoken too soon. It seems to work fine if there are only one or two matches on Col1 and Col2 but if there are more than two it looks like it is not getting everything. I made queries for both tables and then each table and sorted by Col1 to check for matches and in some cases there are 5-10 matches that are not showing up, or I can't see any reason why they would not. Should col1 or col2 be indexed?

Answer 2

You should be able to link to the Excel worksheet, and use the linked table rather than the imported table.

Answer 3

Thank you very much, I was able to get it to work.

Since the source is a temporary table imported from an excel spreadsheet, is there a way to do the update query using the spreadsheet instead of importing, doing the update and deleting the source table?

Answer 4

As Tom points out this can be done entirely as set operations.

Let's assume a simple scenario where a 'match' requires matches on two columns Column1 and Column2, and you wish to update the values in three columns in the target table, Column3, Column4 and Column5, with the values of the same three columns in the source table where there is a match on Column1 and Column2, but not on Column3, Column4 and Column5

You don't really need to concern yourself with whether there is a mismatch on Column3, Column4 and Column5.  Simply updating all rows where there is a match on Column1 and Column2 is enough as any updated rows where there is no mismatch on Column3, Column4 and Column5 will simply be updated to their existing values.  So the query would be like this:

UPDATE TargetTable INNER JOIN SourceTable

ON TargetTable.Column1 = SourceTable.Column1

AND TargetTable.Column2 = SourceTable.Column2

SET TargetTable.Column3 = SourceTable.Column3,

TargetTable.Column4 = SourceTable.Column4,

TargetTable.Column5 = SourceTable.Column5;

Can there also be new rows in the source table which are not yet in the target table, i.e. have no matches on Column1 and Column2?  If so do you need to append these rows to the target table?  This would need a second query:

INSERT INTO TargetTable(Column1,Column2,Column3,Column4,Column5)

SELECT Column1,Column2,Column3,Column4,Column5

FROM SourceTable

WHERE NOT EXISTS

     (SELECT *

      FROM TargetTable

      WHERE TargetTable.Column1 = SourceTable.Column1

      AND TargetTable.Column2 = SourceTable.Column2);

For efficiency you should ensure that Coumn1 and Column2 are each indexed none-uniquely.

However, this is based on a number of assumptions which may or may not be valid:

1.  That Column1 and Column2 constitute a candidate key of both tables, i.e. in each the values of these columns in combination are distinct.

2.  That the values which you wish to update are not in either of the columns which make up the candidate key, i.e. Column1 and Column2.

It goes without saying that before executing set operations of this nature it is imperative that the target table be securely backed up.

Answer 5

There is a solution with 0 VBA. Rather create one or more Update queries that update fields when the joined field(s) are equal.