Results 1 to 4 of 4
  1. #1
    tlghey is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    2

    Update Table with most recent data from other table

    Hello everyone,

    I have been trying not to ask this as long as I could but I am getting pretty desperate now, there just does not seem to be a straight forward way of doing this....or I can't find it on the web...
    I have been making queries and inserting sql statements but I am at a point that I just don't know where to go next, so any leads are most appreciated.

    I have two tables Table1 and Table2 in an access database who have the same column data. Table2 is a table that is often renewed with updated locations, dates and name, sometimes the same item has been moved several times. I would like to update Table1 with the most recent information of Table 2.

    Code:
    
    
    Code:
    Table1 
    BarCode Location    MoveName    Movedate
    245208  My Bench    an          24/11/2016
    350474  room2       Tom         24/11/2016
    350495  My Bench    mieke       04/08/2015
    350500  room 4      Tom         17/10/2016
    
    Table2
        F1  F2          F3       F4
    350500  My Bench    Gert    24/11/2017---most recent
    350495  room 8      Mieke   20/11/2016
    350495  My Bench    Tom     30/11/2017---most recent
    350500  room3       jan     17/10/2016
    350495  living      Tom     4/08/2015
    
    Solution
    Table1
    BarCode Location    MoveName    Movedate
    245208  My Bench    an          24/11/2016
    350474  room2       Tom         24/11/2016
    350495  My Bench    Tom         30/11/2017--updated350500  My Bench    Gert        24/11/2017--updated



    Thank you for any hints! I just can't believe I am stuck at this but after three days searching the net and trying to use SQL in access I don't know anymore what to do or what to look for.

    My code in SQL was:
    Code:
    UPDATE Table1JOIN (SELECT F1, Max(F4) AS maxf4 
    FROM Table2
    GROUP BY F1) xxx ON Table1.BarCode = xxx.F1
    SET Table1.Location=xxx.F2, Table1.MoveName=xxx.F3, Table1.Movedate=xxx.maxf4;
    However, this is apparently not for access...?

    Thanks!

    down votefavorite
    Good evening everyone,
    I feel pretty embarrassed asking this but I have been stuck at this for a few days now and am pretty desperate. I have found very similar problems that have been answered before but the problem is that I do not seem to be able to understand what is happening: How to update another table with the most recent data in SQL?
    So I have two tables Table1 and Table2 who are identical in column headers. Table2 is a table that is often renewed with updated locations, dates they were moved and by whom, sometimes the same item has been moved several times. I would like to update Table1 with the most recent information of Table 2.
    Table1
    BarCode Location MoveName Movedate
    245208 My Bench an 24/11/2016
    350474 room2 Tom 24/11/2016
    350495 My Bench mieke 04/08/2015
    350500 room 4 Tom 17/10/2016

    Table2
    F1 F2 F3 F4
    350500 My Bench Gert 24/11/2017---most recent
    350495 room 8 Mieke 20/11/2016
    350495 My Bench Tom 30/11/2017---most recent
    350500 room3 jan 17/10/2016
    350495 living Tom 4/08/2015

    Solution
    Table1
    BarCode Location MoveName Movedate
    245208 My Bench an 24/11/2016
    350474 room2 Tom 24/11/2016
    350495 My Bench Tom 30/11/2017--updated
    350500 My Bench Gert 24/11/2017--updated



  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You'll need a few queries,
    Q1 to get the Max date: select F3 ,max(f4) from table .
    then make Q2 to join the table to Q1 and pull all records in table that match Q1.
    Make A temp table (make or append) to write these records,because you cannot Sum and update in the same query.

    now this temp table has all the most recent records,make Q3 to update your other table.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Based on your example tables/data, try this query on a COPY of your database.

    Code:
    UPDATE Table1 INNER JOIN Table2 ON Table1.BarCode = Table2.F1 
    SET Table1.Location = [Table2].[F2], Table1.MoveName = [Table2].[F3], Table1.MoveDate = [Table2].[F4]
    WHERE (([MoveDate]<[Table2].[F4]));

  4. #4
    tlghey is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2016
    Posts
    2
    Dear Ranman, SSanfu,

    Thank you both for replying and helping me out! You can not imagine how much I appreciate this!

    @ Ranman: I was indeed trying it this way as well but it was so cumbrous that I did not believe this was the best way. Also I wanted this to happen automatically and was seeing some problems with this approach.

    @ssanfu: man, thank you soooo much!! This is absolutely perfect and exactly what I wanted to do! I was so afraid this morning and thinking of other ways to implement this eg with vb code, but when I saw your message this morning I was so delighted!
    It works brilliantly! I cannot believe no-one has come up with this solution yet, as far as I have seen the threads on this on the internet (and believe me I have seen a lot of them!!)

    Very elegant and simple and works like a charm! thanks a million!
    If you ever need help with science or research let me know (I believe these are my stronger points, stronger than coding anyway )

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-10-2015, 08:56 AM
  2. Replies: 5
    Last Post: 08-25-2015, 08:39 AM
  3. Replies: 1
    Last Post: 12-08-2014, 06:16 PM
  4. Replies: 6
    Last Post: 05-10-2012, 08:20 PM
  5. Pulling most recent data from child table
    By davidv43 in forum Access
    Replies: 0
    Last Post: 02-07-2009, 11:36 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums