Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    append query (2002)

    Hi All, I know how to use an append query to update new (unique) records from a table in one database to an identical table in another database. This works well. But how should I go about transfering updated (or changed) records from one table into the other?
    Regards, Van

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: append query (2002)

    What is the condition that determines updated (or changed) record in the source database?

    Once that is determined you should use an UPDATE query to update the target table.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query (2002)

    Hi Pat,

    Sorry for the delay...i was out of my office. I'm not sure what is meant by your question. If for example a piece of data in a record in the source table is changed (ie. weight changed from 75 to 80 kgs), I need this to then be changed in the target table when I update the target. There are about 100 individual items in the table, and anyone (or more) may have changed since the previous update.

    Thanks,
    Van

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: append query (2002)

    Is there a field that uniquely identifies the records in the table?

  5. #5
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query (2002)

    the PK's are surname, given, and labsite. However, I'm thinking what Pat is getting at is perhaps a way of identifying if a record has been changed. I could use a time stamp (ie. date_modified) to identify the changed records. would this work?
    Thanks,
    Van

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: append query (2002)

    You'd still need the primary key fields to know which records match. But having a field that records the last modified date of a record helps. Note that you'd have to set this field in the Before Update event of the form used to edit the records - Access doesn't have triggers at the table level to update this field.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query (2002)

    I've got all that working, but am having trouble with the update query in design view. I've added the two tables (souce and destination) and I've created the joins for the Primary Key fields (3). I've dragged all the fields from the destination (the table to be updated) down to the design grid. How do I go about telling Access that I only want the records where the modified date don't match to be updated?
    Cheers,
    Van

  8. #8
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query (2002)

    Hi again Hans,

    I've got it work thanks. Looks like I will have to drag each field indidually to the design grid (can't use the * ) then set the update to for each field as well. Well over 100 fields! ugh.
    Many thanks for you great help.
    Cheers,
    Van

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: append query (2002)

    Let's say that the last modified date/time field is named ModifiedDate.
    In the first criteria row for this field, enter

    <> [SourceTable].[ModifiedDate]

    where SourceTable is the name of the source table (the table you're updating from).

  10. #10
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query (2002)

    Thanks muchly Hans, that works perfectly. Do I need to drag each invidual field to the design grid then set the update to: criteria for each field? (i've got probably over 150 fields!).

    Van

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: append query (2002)

    Yes, you must specify each individual field. Wouldn't it be easier to delete all records from the destination table, or those without a match in the source table, then append all records from the source table to the destination table?

    (Having over 150 fields in a table is a *lot* - in many situations it's possible to redesign such a table to use many fewer fields, but it depends on the nature of the fields, of course)

  12. #12
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query (2002)

    That's a good solution I think. I'm a bit reluctantly to have these records deleted then created again.....do you think this could cause problems, especially in a few years time when many records may be deleted in one shot?
    Van
    (can't redesign table as each field represents a separate unique entitiy....ie. gluscose level, sodium level, etc)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: append query (2002)

    Deleting and appending many records shouldn't cause problems. It's a good idea to compact the database from time to time.

    If you have tens of test results in the table, a simplified version of your table probably looks like this:

    <table border=1><td align=center>PatientID</td><td align=center>LastName</td><td align=center>Glucose</td><td align=center>Sodium</td><td align=center>..</td><td align=center>Protein</td><td align=right>1</td><td>Jones</td><td align=right>15</td><td align=right>32</td><td align=right>*</td><td align=right>50</td><td align=right>2</td><td>Williams</td><td align=right>18</td><td align=right>27</td><td align=right>*</td><td align=right>37</td></table>
    I'd use a design with three tables instead:

    1) A "persons" table:

    <table border=1><td align=center>PatientID</td><td align=center>LastName</td><td align=right>1</td><td>Jones</td><td align=right>2</td><td>Williams</td></table>
    2) A "test items" table:

    <table border=1><td align=center>ItemID</td><td align=center>Item</td><td align=right>1</td><td>Glucose</td><td align=right>2</td><td>Sodium</td><td align=right>..</td><td align=right>*</td><td align=right>99</td><td>Protein</td></table>
    3) A "test results" table:

    <table border=1><td align=center>PatientID</td><td align=center>ItemID</td><td align=center>Value</td><td align=right>1</td><td align=right>1</td><td align=right>15</td><td align=right>1</td><td align=right>2</td><td align=right>32</td><td align=right>..</td><td align=right>*</td><td align=right>*</td><td align=right>1</td><td align=right>99</td><td align=right>50</td><td align=right>2</td><td align=right>1</td><td align=right>18</td><td align=right>2</td><td align=right>2</td><td align=right>27</td><td align=right>..</td><td align=right>*</td><td align=right>*</td></table>
    As you see, each of the tables has only a limited number of fields.

  14. #14
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: append query (2002)

    Thanks again Hans, much appreciated.

    Regards, Van

Posting Permissions

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