Hello, I have two tables: Records, and Tasks. I am trying to make an append query that adds a new record to the Records table where the dates of the records in the Record table equals today. I am adding a new record with most of the same information of the selected record and appending two fields in the new record. I am appending the HistoryNumber field and the ActualNext field. The HistoryNumber field is the PK and an autonumber so it should increase by 1. The ActualNext field should be appended to Date()+Tasks.Frequency
For example my query selects all records from the Records table where ActualNext=today, and the frequency number is 7)
HistNum:1,TaskNumber:455,ActualNext:1/31/2019
A new record should be added like the following :
HistNum:x,TaskNumber:455,ActualNext:2/7/2019
Here is the sql:
I am able to get a new record to add to the table, but the only field that is changing is the HistoryNumber(PK), how do I add a new record with the ActualNext field = Date()+Tasks.Frequency?
For example my query selects all records from the Records table where ActualNext=today, and the frequency number is 7)
HistNum:1,TaskNumber:455,ActualNext:1/31/2019
A new record should be added like the following :
HistNum:x,TaskNumber:455,ActualNext:2/7/2019
Here is the sql:
Code:
INSERT INTO Records ( TaskNumber, MachineID, AdjusterID, OnTime, ScheduledDate, CompletedDate, SpotNumber, OnTimeNumber, PlantNumber, ActualNext )
SELECT Records.TaskNumber, Records.MachineID, Records.AdjusterID, Records.OnTime, Records.ScheduledDate, Records.CompletedDate, Records.SpotNumber, Records.OnTimeNumber, Records.PlantNumber, Records.ActualNext
FROM Records INNER JOIN Tasks ON Records.TaskNumber = Tasks.TaskNumber
WHERE (((Records.ActualNext)=Date()));
I am able to get a new record to add to the table, but the only field that is changing is the HistoryNumber(PK), how do I add a new record with the ActualNext field = Date()+Tasks.Frequency?