Only update if today's date is not in the table being updated (1 Viewer)

[Gismo]

Hi all,

Please could you assist

I want to update a table from another table
If the record does not exist, it must be updated
If the the record exists and the date updated in the same as todays date in the table being updated (IW73 - Project Leader - History), the record should not be updated

INSERT INTO [IW73 - Project Leader - History] ( Registration, [CS Order], Status, [Start Date], Description, [work], [Actual work], [Total Act Cost] )
SELECT [IW73 TBL - CS Orders].Registration, [IW73 TBL - CS Orders].Order, [IW73 TBL - CS Orders].[OTD Sytem Status], [IW73 TBL - CS Orders].StartDate, [IW73 TBL - CS Orders].Description1, [IW73 TBL - CS Orders].work, [IW73 TBL - CS Orders].[Actual work], [IW73 TBL - CS Orders].[TotSum (actual)]
FROM ([IW73 TBL - CS Orders] LEFT JOIN [Aircraft List] ON [IW73 TBL - CS Orders].Registration = [Aircraft List].[Aircraft Registration]) LEFT JOIN [IW73 - Project Leader - History] ON ([IW73 TBL - CS Orders].Order = [IW73 - Project Leader - History].[CS Order]) AND ([IW73 TBL - CS Orders].Registration = [IW73 - Project Leader - History].Registration) AND ([IW73 TBL - CS Orders].UpdatedDate = [IW73 - Project Leader - History].[Date Updated])
WHERE ((([Aircraft List].Project)=True) AND (([Aircraft List].Active)=True))
ORDER BY [IW73 TBL - CS Orders].Registration, [IW73 TBL - CS Orders].Order;

 

[CJ_London]

If the record does not exist, it must be updated

I presume you mean appended - in which case I would have thought a criteria

[IW73 - Project Leader - History].[Date Updated] is null

but not clear what this means

and the date updated in the same as todays date

if it literally means today then include an additional criteria

[IW73 TBL - CS Orders].UpdatedDate<Date()