Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    30

    Unanswered: updating table with data from another

    Hi

    I've noticed a couple of posts about this subject, however in the contexts they arent helping me much.

    I have a table of data with the unique item being a reference number. There are several fields of data for the reference number and there can be also be multiple entries for a reference number if the fields are changed at a later date.

    eg.
    Ref Number|data item 1|data item 2 etc| entry date
    1584541228 | abc | abc1 | abc2 | 01/01/2008
    1584541228 | abc | abc2 | abc1 | 19/11/2009
    4548645454 | gdk | abc2 | anc3 | 01/11/2009

    Each month we get a new set of reference numbers which are loaded into another table. What I need to do is check the new list of reference numbers against the old one that contains all the data, and if theres a match, insert the latest data into the new table.
    eg.
    1584541228 | | | |
    4548645454 | | | |
    For ref 1584541228 I would need to insert the info from the archive table into the live table for just the entry date 19/11/2009, and 4548645454 all the data as theres only one record for that one.

    I've been playing around with Append and Update queries but cant quite get it right. The nearest I got, I got an error with the SQl saying "subquery without EXISTS statement". There must be an easier way, and im at a dead end.

    The SQL I got for it is as follows:

    UPDATE no_aa_archive INNER JOIN no_aa_dayfile

    ON no_aa_archive.REF_NO = no_aa_dayfile.REF_NO

    SET
    no_aa_dayfile.[Next Action Date] = no_aa_archive.[Next Action Date],
    no_aa_dayfile.[Last Action Date] = no_aa_archive.[Last Action Date],
    no_aa_dayfile.[Resolution Date] = no_aa_archive.[Resolution Date],
    no_aa_dayfile.[Action Taken] = no_aa_archive.[Action Taken],
    no_aa_dayfile.Comments = no_aa_archive.Comments,
    no_aa_dayfile.Name = no_aa_archive.Name,
    no_aa_dayfile.[Report Month] = no_aa_archive.[Report Month],
    no_aa_dayfile.[Date Taken On] = no_aa_archive.[Date Taken On]

    WHERE (((no_aa_dayfile.[Next Action Date]) Is Not Null));

    So I need the [no_aa_archive].[Report Month] to be maxed so it brings back the specific last one as I mentioned above.

    Any help would be appreciated.

    Cheers
    Last edited by chrisatwork; 11-25-09 at 06:53.

  2. #2
    Join Date
    May 2007
    Posts
    27
    Chris,

    Correct me if I am wrong, but you are never INSERTING data in the new live table, right? You are only UPDATING the live table with information from the archive table, if the reference number matches with a number in the archive table.

    And then, if you find a match, you want to grab the latest information from the archive table, and update the live table with that. Is that about correct?

    Wim

  3. #3
    Join Date
    May 2009
    Posts
    30
    Wim,

    Yep thats pretty much it.

    Thanks

  4. #4
    Join Date
    May 2007
    Posts
    27
    Good, so one additional question before we can get solutions.

    Does the live table have the same fields as the archive table, or do those fields need to be created dynamically?

    This is a big difference, because if the fields already exist, then we can use UPDATE combined with some creative joins.

    If the fields don't exist, then vba will likely be needed to get this working.

  5. #5
    Join Date
    May 2009
    Posts
    30
    Hi Wim,

    Yep the tables are identical.

    Thanks for the help.

  6. #6
    Join Date
    May 2007
    Posts
    27
    Hmmm. I am having problems writing the sql statement for this (which is probably exactly what you have been encountering to begin with...).
    I am at work right now, so I can't dedicate the time to it that it needs. I will look into this again this evening, but it should be a fairly easy sql query.
    One additional question though: does each row have a unique ID (autonumber) in both tables?

  7. #7
    Join Date
    May 2009
    Posts
    30
    No I dont include an autonumber as I thought the reference numbers would be sufficient. Is it still possible?

  8. #8
    Join Date
    May 2009
    Posts
    30
    Anybody else have any suggestions?

Posting Permissions

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