replace existing record with updated record

N

NewtoComputing

I have a master table with each row representing a unique record. My
colleagues need to have exact copies of this table in which they can alter
any of the unique records and then send their altered copy back to me. The
altered records from each copy then needs to replace its matching record in
the master table.

Can this be done using a set of simple step-by-step instructions (query
builder etc.)?
 
M

mscertified

What will you do if two colleagues modify the same record?

Your table needs to have a timestamp column which is updated whenever the
record is updated. Then when your colleagues ship you the table, you need to
replace any record where the timestamp is later than the current one. Sounds
like you might also need an 'UpdatedBy' column, yso you can track who last
changed a record.

This all sounds like a weird arrangement. Are you sure there is not a better
way, like making the db multi-user and just have everyone access it.

-Dorian
 
K

Klatuu

You can create an update query to do this.
Open the query builder.
Add your main table.
Add the other table. (do this one other table at a time)
Create a join between the two tables. Select 2 as the Join Type.
In the first row, enter the name of all fields in your main table. If your
tables have an autonumber field, do not include that field.
In the Update To row, enter the name of each field in the other table below
the same field name of the main table.

When you run the query, it will update the field values in the main table
from other table.
 
N

NewtoComputing

Thanks mscertified. I know it is crazy, but only one person is allowed to
edit the master database - a system that has been running for 16 years. As a
lowly assistant, I can only try and change the way things are done bit by bit.
 
N

NewtoComputing

Thank you so much Klatuu! I was beginning to think I needed a degree in
computer science.
This worked as long as I put [tablename].[fieldname] in the "update to" row
as well as following your instructions.
Thanks again.
 
K

Klatuu

Sorry, I did leave that out of my instructions.
--
Dave Hargis, Microsoft Access MVP


NewtoComputing said:
Thank you so much Klatuu! I was beginning to think I needed a degree in
computer science.
This worked as long as I put [tablename].[fieldname] in the "update to" row
as well as following your instructions.
Thanks again.


Klatuu said:
You can create an update query to do this.
Open the query builder.
Add your main table.
Add the other table. (do this one other table at a time)
Create a join between the two tables. Select 2 as the Join Type.
In the first row, enter the name of all fields in your main table. If your
tables have an autonumber field, do not include that field.
In the Update To row, enter the name of each field in the other table below
the same field name of the main table.

When you run the query, it will update the field values in the main table
from other table.
Click to expand...
Click to expand...
 
Top