Update one table with data from another table based on a query

[Azzna]

I am at a loss on how to handle this problem.

I have a database I am creating in Access to compare two sets of data
(From different databases) and show me any data errors. The data is
coming in via CSV file. Futhermore, the data I receive is not in a
format I can use, nor is it normalized. I created a series of queries
that run off a VBA form by pressing a button that normalize the data
for me. Once I am done normalizing the data, I have another button
that will add any new records from the last time I have run it to a
master table, (The master table holds all records and will contain a
history of records also. This is new since my department until now
would just make a new copy of the database for each product line, and
so it was close to impossible to get any kind of history for the year)
and deactivate any records that have since been deactivated. However,
if a change was made to an active file, I need to update the table that
holds all my information (Which is a seperate table from the imported
table).

I have a query that identifies all the records that need to be updated
because they have had a change.

I was thinking I would set up a do while for when the update query is
not end of file. As long as it wasn't I would set up an update
statement to change the data. So, my code would look kinda like this:

DO While NOT ChangeQuery.EOF

Update MasterTable
Set MasterTable.Field1 = ChangeQuery.Field1,
MasterTable.Field2 = ChangeQuery.Field2,
MasterTable.Field3 = ChangeQuery.Field3,
MasterTable.Field4 = ChangeQuery.Field4



I figured on creating variables to hold the ChangeQuery data and have
it populate via the Do While loop each round. However, I am not sure
this is the best way to handle this. Any ideas or help would be
greatly appreciated.

 

[Jeff Boyce]

If you can create a query that returns the entire set of data you need to
add, use an Append query (or an Update query, as appropriate), rather than
looping through a recordset.

Or maybe you can only build the recordset via code (looping rather than
query/set operations will be slower)...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Azzna]

Thank you, I was able to do it as an update statement via join.