Update Employees Payroll Data

[burrina]

qryUpdateEmployeePayrollData.jpg

Every year I have to update the employees payroll data to the latest data entered. So I have to check first if it is January 1 of the current year and if so then run either a query or use vba instead. Unsure of whether to do this automatically or manually by way of a reminder.

Here is a screenshot of the tables used doing this. The payroll data is entered on the employees form and using 3 other subforms for payroll data.
The payroll data is looked up on the payroll data entry form when a employee is selected to write a payroll check.

Any suggestions?

Temporarily Shelved Until I Calculate some code.

Thanks,

[HansV]

It's not clear to me what is being updated to what. Can you provide more specific information?

[burrina]

On my Employees form is where the Payroll data is entered for each employee. Their PayRate,Single/Married Status,Number of Allowances taken.
This needs to be updated every year with the latest data entered. For instance a employees pay rate may have been 10hr in 2015 but 11hr in 2016 as wells their single/married status and their number of allowances taken.

[HansV]

Do you want to update tblEmployees with the most recent values in TPayRate, TNoAllow and TSinMar?

[burrina]

No. That's the thing, there is no single table that needs to be updated. Instead 3 tables need to be updated for each employee.
TPayRate
TNoAllow
TSinMar

All 3 tables above need to be updated for each employees payroll data. New record for each employee with the latest data.
1 main form, 3 subforms.
frmEmployees
FNoAllowSub
FPayRateSub
FSinMarSub

[HansV]

Are these subforms bound to the corresponding tables (i.e. FNoAllowSub to TNoAllow etc.)?

[burrina]

YES. Via EmployeeID and EmpID
Master/Child

[HansV]

I'd say you don't need an append query. Simply create a new record in each of the subforms. The new records will automatically be saved in the underlying tables.

[burrina]

No. I need the old/latest data to be put into a new record (Subforms) for each employee if it is January 1 of the current year and if they have not been already entered.

[HansV]

Do you mean that you want to duplicate the most recent record for each employee?

[burrina]

The EffDate of course needs to be updated to the latest date for TPayRate table and then the latest data from the other 2 tables added to the other tables with the latest data. Sorry for my bad explanations.

[HansV]

I don't see the point. For example, if the pay rate changes, you add a new record. You would do that in the pay rate subform, and it would automatically be added to TPayRate. If it doesn't change compared to last year, there is no need to add a new record - the most recent pay rate will still be valid.

[burrina]

BUT, it is more complex than that. Those forms are linked to the tax tables. Anyway, it would take a long time to do this if there were very many employees.
Hence the automation needed. I realize this may not make a whole lot of sense without going into the nuts and bolts of the db. It is VERY complex.
Thanks,

It is best to helve this for now until I can think more about this.

Thanks so much for the help.

[HansV]

The subforms are NOT linked to TNoAllow, TPayRate and TSinMar? I'm lost...