Update query criteria, most recent item?

[King_kamikaze]

Hello everyone,

I have got an update query that i use to update a main table. The query uses two tables, "StartingPoint" & "UpdateFuture". "Reference" is the primary key in StartingPoint and is also in UpdateFuture table but not as a primary key because there can be multiple instances of the Reference.

Every record in UpdateFuture has a date that it was imported into the table, if the UpdateFuture table has two instances of the same reference i would like the query to only update StartingPoint with the most recent one according to the DateImported field.

Any help on this would be greatly appreciated and i am sorry if you cant quite understand what i am looking to achieve, please ask if you need me to clarify anything.

Many thanks
Tim

 

[Jon K]

You can do it with a series of 3 queries.

I have attached a sample database. Run the third query in it.
.

 

[King_kamikaze]

Thanks for the demo, excellent suggestion, am now currently trying to get it to work with my DB.

Many thanks.
Tim

 

[King_kamikaze]

Hiya,

Once again thanks for your excellent suggestion, the only problem i am having (Probably should have mentioned this before), i need to set another criteria for the update, it wont let me do this at all.

Here is an example of what i need to update.

UPDATE StartingPoint INNER JOIN qry2 ON StartingPoint.Reference = qry2.Reference SET StartingPoint.DateField = [qry2].[DateImported], StartingPoint.OtherFields = [qry2].[OtherFields]
WHERE (([StartingPoint]![DateField]="11/04/05"));

Basically these is a second date filed called EffectiveDate, this will be the last day of a month, in my previous query i set the date i want to update using a form but it will not let me set any kind of criteria in the update query!

Sorry to be a pain but any help would be excellent.
Thanks
Tim

 

[EMP]

UPDATE StartingPoint INNER JOIN qry2 ON [StartingPoint].[Reference]=[qry2].[Reference] SET StartingPoint.DateField = [qry2].[DateImported], StartingPoint.OtherFields = [qry2].[OtherFields]
WHERE [StartingPoint].[DateField]=DateValue('11/04/2005');


For a table field, use . intead of !
For UK date format of dd/mm/yyyy, use DateValue('11/04/2005')