Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update/Modified last record (Access 97/sr2)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update/Modified last record (Access 97/sr2)

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

  3. #3
    Star Lounger
    Join Date
    Sep 2001
    Location
    SC, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update/Modified last record (Access 97/sr2)

    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.

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update/Modified last record (Access 97/sr2)

    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

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Indiana, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update/Modified last record (Access 97/sr2)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •