Append Query with updated fields

[duncan17]

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:

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?

 

[pbaldy]

You can use those fields in the DateAdd() function, just hardcoding the interval.

 

[duncan17]

I would hardcode the interval but most records have a different interval number, that is why I am trying to set the ActualNext date = Date() + [Tasks].[Frequency] using the Frequency field from a separate table.

 

[pbaldy]

I meant the interval of days/months/etc, but that could also come from the data. I assumed it was a constant.