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:
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!
|
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
|