Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    41

    Unanswered: Maintain data after Import

    I have to occasionally import 3 new data columns from tblX to tblY, however, tblY contains data that is manually input that I do not want to lose. I have tried moving the manual input data to a temp table and then back after the import, but only end up with dup rows, etc... Do I need Primary Keys set upa certain way, or is it dependant on the relationship between the two tables (i.e. Referential Integrity, Cascade update, etc..?
    I would greatly appreciate any assistance!
    REG

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Prep your data using a temporary table. Left join tblX and tblY together, selecting only the values where tblY is null. That represents the data that needs to be updated.

    If you are just adding columns, use an update query.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2004
    Posts
    41
    Appreciate the reply ... I should have been more specific in the original post (I was pulling an all-nighter).

    I am dealing with individual records identified by a unique customer ID number. So, should I move the manual input data to a temp table, import their 3 additional info columns (downloaded from a main data system) and then move the "manual input" data back? If so, does the cutomer ID number need to be primary key in both? I don't think I can use an update query because that requires you update an entire column (for all records) to the same value, right?

    Thx.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Sounds like an update query. You can use values from another table in an update query, they do not have to be static.

    For example, you could use something similar to the following:

    UPDATE tableX INNER JOIN tableY ON someKey
    SET tableY.someField = tableX.someField
    WHERE tableY.someField IS NULL

    That's not the exact syntax, but hopefully you get the idea. You want to join the two tables together, then use values from a given record in tableX to update the corresponding record in tableY
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Dec 2004
    Posts
    41

    Thumbs up

    Teddy,
    I am very new to the SQL side of queries ... the light is dim but it is getting brighter! I made it work on the front side of the query though using your recommendation.
    (note to self ... must learn SQL)

    Cheers, Reg

  6. #6
    Join Date
    Aug 2003
    Location
    Cleveland USA
    Posts
    184
    Another option is to keep the data in two tables, and link by customer ID. I've done that where one table is for manual entry and the second is for data downloaded from another source. It makes it easier to append, or even use a make table query when it's easier to start over each time.

Posting Permissions

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