Use single entry in one table to generate multiple in another

[tbrogdon]

I have tblProduction w/ ProdDate, Department, Shift (composite pk -all
3 fields)

I have tblEmployees w/ EmployeeID (pk), FirstName, Lastname,
Department, and Shift.

I have tblDepartment w/ DepartmentID.

I have tblShift w/ ShiftNumber and ShiftHours (which vary between
shifts but are constant within shifts).

I also have tblEmployeeProduction w/ ProdDate, EmployeeID, Department,
Shift, TotalHoursWorked.

I have frmProduction which creates an entry in tblProduction. It has
cmdAddDetails which currently opens a detail form to input production
details by part which updates yet another table. This all works well.

I would like cmdAddDetails to make an entry for every employee for
that ProdDate, Department, and Shift and populate the TotalHoursWorked
field with the default number of hours from Shift.ShiftHours. In other
words, if I have 35 employees in one dept. on a given shift, I will
have 35 entries in tblEmployeeProduction fully populated when
cmdAddDetails is clicked.

Can someone point me in the right direction?

Thanks,

Tim

 

[Ken Sheridan]

Tim:

If I've understood the relationships correctly, try executing the following
query in the button's Click event procedure. Make sure that the code saves
the current tblProduction record first:

INSERT INTO tblEmployeeProduction
(ProdDate, EmployeeID, Department, Shift, TotalHoursWorked)
SELECT ProdDate, EmployeeID, tblShift.ShiftNumber, tblShift.ShiftHours
FROM tblEmployees, tblShift, tblProduction
WHERE tblEmployees.Shift = tblShift.ShiftNumber
AND tblProduction.Shift = tblShift.ShiftNumber
AND ProdDate = Forms!frmProduction.ProdDate
AND tblEmployees.Department = Forms!frmProduction.Department
AND ShiftNumber = Forms!frmProduction.Shift;

BTW I think the Department column in tblEmployeeProduction is redundant as
it is implied by the EmployeeID column.

Ken Sheridan
Stafford, England