Update/Modified last record (Access 97/sr2)

Topic

New Reply

I think I left my brain on vacation!!! Here’s the situationI have a database that is updated on a bi-weekly basis. I created a query that makes a master of all the files imported. I have 4 files that are imported. Some of the data will need to be modified manually and the other data will come in when I run the imports and then the make table query. My question is this: How do I keep access from overwriting the data that I manually updated when I run the query? Should I create an Append query and not write the data if the primary key is the same? However, if the data changed in another system, I want the most recent update of all files. Should I create an update query? Maybe I should create a union of the two tables and then extract the most recently updated record? HOW??

I know there is a way but I am having a hard time figuring it out this morning. Any and all suggestions will be GREATLY appreciated!!!

Thanks,
DGP

Reply | Quote

Replies

This sounds like a situation that would benefit from replication rather than manual or semi-automated updates. Have you considered that option?

Reply | Quote

I don’t think I made myself real clear. Our pay period run every 2 weeks so I need an update from payroll to add new employees and remove terminated employees. Another system is updated daily but I only get the download every 2 weeks – again I need the new information. There are a few fields like Qualifications, License # that is only updated when the Director receives this information – so this must be hard keyed somewhere. If I import all my downloads and use a make query table it overwrites the hard keyed data. How do I keep the keyed data and bring in the imported data?

This is what I did yesterday. I made a master of the first downloads and I use this file to hard key in as well as using it as a base. Next, I create another file called updated master, union the master and updated master and then I build a make table query to check for Last under the hard keyed fields and generate another table that I pull reports from and this seems to work. I am in the process of testing today. If you have any other suggestions, please do not hesitate to reply.

Thanks,
Deborah

Reply | Quote

Maybe you need to place the fields that your Director keeps current in a separate table. You could then link this table to your tables that have to be updated and you wouldn’t over write the Director’s info.

Reply | Quote

What application do the 4 files come from? If they come from 4 users who are simply using a copy of your “master” database, then Charlotte is correct about replication. If you don’t like that answer, how about sharing the database with the users or splitting your database and giving the users their own “front ends” pointing to your “back end”. Unless there is some reason (security?) to restrict access to all files in your table, just let the users update the data directly. If you’re worried about the master database, just be sure it’s backed up frequently.

Reply | Quote