Reset multiple fields for each Employee 1

[dcurtis]

I have a DB that tracks OT worked by each employee during the FY. Just 2 tables (tblOverTime (OT_ID, WorkDate, WorkEmployee, WorkHours) and tblEmployeeData (EmployeeID, Name, Rank, Seniority, Phone)).

This is used to decide who gets called for the next available OT shift.

On July 1st of each year I need to reset each employee back to 0, with a WorkDate of 7/1/Current Year. I need the values to be in tblOverTime because a report that is generated to see who gets the next shift defaults to seniority if everyone has 0 hours (and it needs to have everyone in the table to show up in the report). Also, I need to restrict the EmployeeID's' that get added to 2 groups of employees (Firefighters and Captains). tblEmployeeData has other ranks in it, but the OT portion applies only to FF's and Captains.

I have tried several variations of queries (update, append, delete (all previous data in the table) to get the right data in there but just can't seem to make it work.

Thanks in advance for any and all help.

 

[KenReay]

Hi

If I Understand you right you want to delete all rows in tblOvertime, then Add one row per EE to tblOvertime, Correct?

DELETE FROM tblOvertime;

INSERT INTO tblOvertime SELECT #2005/07/01# As WorkDate, EmployeeId, 0 As WorkHours FROM tblEmployeeData;

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dcurtis]

Exactly what I was looking for, thanks!