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 Access. Can I do this function in the query itself?

Fionnuala · Accepted Answer · 2010-01-20 22:11:23Z

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"

edited Jan 20, 2010 at 22:11

answered Jan 20, 2010 at 20:28

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using loop in a Access Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related