Append Query with updated fields (1 Viewer)

duncan17

Registered User.
Local time
Today, 06:20
Joined
Jan 29, 2019
Messages
10
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:
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?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:20
Joined
Aug 30, 2003
Messages
36,118
You can use those fields in the DateAdd() function, just hardcoding the interval.
 

duncan17

Registered User.
Local time
Today, 06:20
Joined
Jan 29, 2019
Messages
10
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

Wino Moderator
Staff member
Local time
Today, 06:20
Joined
Aug 30, 2003
Messages
36,118
I meant the interval of days/months/etc, but that could also come from the data. I assumed it was a constant.
 

Users who are viewing this thread

Top Bottom