Using loop in a Access Query

Question

I have a table named OT Hours which has the following column EmpId, Date, Hours.

I need to insert a constant value in the hour column for 12 months prior to the current date for 6 employees.

Can I use a for loop in the query? If yes, Please provide me with an example. as of now, i can do it in VBA as follows

  Dim j As Integer
  For j = -11 To 0
    DoCmd.RunSQL "INSERT INTO tblOTHours (employeeNumber, theDate, HoursType, Position, hoursQuantity) VALUES ('" & S.sanitize(txtEmployeeNumber) & "',DateAdd('m'," & j & ",Format(Now(),'mm/dd/yyyy')),'OT1','" & cmb_position.value & "'," & Round(Val(rs("Avg")) / 12, 1) & ")"
  Next

NOTE. i AM USING ms acess. can i do this funtion in the query itself????

Answer 1

It can be useful to have a numbers table that holds integers from 1 or 0 to a suitably high number. Your query could take advantage of this table like so:

"INSERT INTO tblOTHours (theDate, employeeNumber, HoursType, [Position], hoursQuantity) " _
& "SELECT DateAdd('m',Number,Date()), '" & S.sanitize(txtEmployeeNumber) & "','OT1','" _
& cmb_position.value & "'," & Round(Val(rs("Avg")) / 12, 1) _
& " FROM Numbers " _
& "WHERE Numbers.Number<11"