Join tables and get 'last' record

[pdauction]

Hi,



Really hope there is someone who can help me on this...

I have a 'master' table called A_to_B_Movements and a Movements_Xtra table. All records have a unique indicator - UID.

What I want to do is add in the Movemnts_Xtra table to the A_to_B_Movements table and for each record to replace the existing one in the A_to_B_Movements table if one exists or just be added in if one doesn't exist.

I have tried to do this using a union query first and then a make table query that gets the last record.

Here is the Union query - I set up a 'From_File field as either 0 or 1 depending on which table.

SELECT UID, mvttyp, brfdte, bexdte, offdate, effdte, aclieno, adob, asex, disdefcd, asmoko, bclieno, bdob, bsex, disdef2, bsmoko, autouw, propsrce, comearn, pcode, polreltp, polrelno, benchtyp, prefre, A__CSA, A__pretot, A__prebas, A__agntnoc, A__Rated1, A__Rated2, A__asmokc, A__bsmokc, A__aocclasc,  A__bocclasc,  B__CSA, B__pretot,  B__prebas,  B__agntnoc,  B__Rated1, B__Rated2, B__asmokc,  B__bsmokc, B__aocclasc,  B__bocclasc, rec__name,0 AS From_File
FROM A_to_B_Movements
UNION SELECT UID, mvttyp, brfdte, bexdte,"" AS offdate, effdte, aclieno, adob, asex, disdefcd, asmoko, bclieno, bdob, bsex, disdef2, bsmoko, autouw, propsrce, comearn, pcode, polreltp, polrelno, benchtyp, prefre, 0 AS A__CSA, 0 AS A__pretot, 0 AS A__prebas, "" AS A__agntnoc, "" AS A__Rated1, "" AS A__Rated2, "" AS A__asmokc, "" AS A__bsmokc, "" AS A__aocclasc, "" AS A__bocclasc, 0 AS B__CSA, 0 AS B__pretot, 0 AS B__prebas, "" AS B__agntnoc, "" AS B__Rated1, "" AS B__Rated2, "" AS B__asmokc, "" AS B__bsmokc, "" AS B__aocclasc, "" AS B__bocclasc, "" AS rec__name, 1 AS From_File
FROM Movements_Xtra
ORDER BY uid, From_File;

Here is the Make Table where I want to get the last/latest record to ultimately replace/get rid of the existing record if one exists;

SELECT Join_A_to_B_Movements_and_Movements_Xtra.UID, Last(Join_A_to_B_Movements_and_Movements_Xtra.mvttyp) AS mvttyp, Last(Join_A_to_B_Movements_and_Movements_Xtra.brfdte) AS brfdte, Last(Join_A_to_B_Movements_and_Movements_Xtra.bexdte) AS bexdte, Last(Join_A_to_B_Movements_and_Movements_Xtra.offdate) AS offdate, Last(Join_A_to_B_Movements_and_Movements_Xtra.effdte) AS effdte, Last(Join_A_to_B_Movements_and_Movements_Xtra.aclieno) AS aclieno, Last(Join_A_to_B_Movements_and_Movements_Xtra.adob) AS adob, Last(Join_A_to_B_Movements_and_Movements_Xtra.asex) AS asex, Last(Join_A_to_B_Movements_and_Movements_Xtra.disdefcd) AS disdefcd, Last(Join_A_to_B_Movements_and_Movements_Xtra.asmoko) AS asmoko, Last(Join_A_to_B_Movements_and_Movements_Xtra.bclieno) AS bclieno, Last(Join_A_to_B_Movements_and_Movements_Xtra.bdob) AS bdob, Last(Join_A_to_B_Movements_and_Movements_Xtra.bsex) AS bsex, Last(Join_A_to_B_Movements_and_Movements_Xtra.disdef2) AS disdef2, Last(Join_A_to_B_Movements_and_Movements_Xtra.bsmoko) AS bsmoko, Last(Join_A_to_B_Movements_and_Movements_Xtra.autouw) AS autouw, Last(Join_A_to_B_Movements_and_Movements_Xtra.propsrce) AS propsrce, Last(Join_A_to_B_Movements_and_Movements_Xtra.comearn) AS comearn, Last(Join_A_to_B_Movements_and_Movements_Xtra.pcode) AS pcode, Last(Join_A_to_B_Movements_and_Movements_Xtra.polreltp) AS polreltp, Last(Join_A_to_B_Movements_and_Movements_Xtra.polrelno) AS polrelno, Last(Join_A_to_B_Movements_and_Movements_Xtra.benchtyp) AS benchtyp, Last(Join_A_to_B_Movements_and_Movements_Xtra.prefre) AS prefre, Last(Join_A_to_B_Movements_and_Movements_Xtra.A__CSA) AS A__csa, Last(Join_A_to_B_Movements_and_Movements_Xtra.A__pretot) AS A__pretot, Last(Join_A_to_B_Movements_and_Movements_Xtra.A__prebas) AS A__prebas, Last(Join_A_to_B_Movements_and_Movements_Xtra.A__agntnoc) AS A__agntnoc, Last(Join_A_to_B_Movements_and_Movements_Xtra.A__Rated1) AS A__Rated1, Last(Join_A_to_B_Movements_and_Movements_Xtra.A__Rated2) AS A__Rated2, Last(Join_A_to_B_Movements_and_Movements_Xtra.A__asmokc) AS A__asmokc, Last(Join_A_to_B_Movements_and_Movements_Xtra.A__bsmokc) AS A__bsmokc, Last(Join_A_to_B_Movements_and_Movements_Xtra.A__aocclasc) AS A__aocclasc, Last(Join_A_to_B_Movements_and_Movements_Xtra.A__bocclasc) AS A__bocclasc, Last(Join_A_to_B_Movements_and_Movements_Xtra.B__CSA) AS B__csa, Last(Join_A_to_B_Movements_and_Movements_Xtra.B__pretot) AS B__pretot, Last(Join_A_to_B_Movements_and_Movements_Xtra.B__prebas) AS B__prebas, Last(Join_A_to_B_Movements_and_Movements_Xtra.B__agntnoc) AS B__agntnoc, Last(Join_A_to_B_Movements_and_Movements_Xtra.B__Rated1) AS B__Rated1, Last(Join_A_to_B_Movements_and_Movements_Xtra.B__Rated2) AS B__Rated2, Last(Join_A_to_B_Movements_and_Movements_Xtra.B__asmokc) AS B__asmokc, Last(Join_A_to_B_Movements_and_Movements_Xtra.B__bsmokc) AS B__bsmokc, Last(Join_A_to_B_Movements_and_Movements_Xtra.B__aocclasc) AS B__aocclasc, Last(Join_A_to_B_Movements_and_Movements_Xtra.B__bocclasc) AS B__bocclasc, Last(Join_A_to_B_Movements_and_Movements_Xtra.rec__name) AS rec__name, Max(Join_A_to_B_Movements_and_Movements_Xtra.From_File) AS From_File, 1 AS paramlink INTO Latest_Movement_with_Xtra
FROM Join_A_to_B_Movements_and_Movements_Xtra
GROUP BY Join_A_to_B_Movements_and_Movements_Xtra.UID;

I have many problems using 'Last' before and still don't fully understand it's use. Using - Max(Join_A_to_B_Movements_and_Movements_Xtra.From_ File) AS From_File - seems to bring in the correct number of 1s under this field but these do not drag in the rest of that record in all cases i.e. it retains the original record in some cases.

Any help is greatly appreciated.

Paul

[orange]

Paul,
Welcome to the forum. Last is not a recommended command/function. There is NO inherent order to records in a relational table. Typically, if someone is looking for the latest record in a table, they are looking for the record with the latest date. So they use a query along this line

Select * from myTable
Where SalesDate = (Select Max(salesDate) from MyTable)

However, based on your field names

...., B__Rated1, B__Rated2,....

you may want to research Normalization.

Good luck