Using data from one table to feed a column in another, but with one limitation that I'm stuck on.

[sharong582]

I'm completely new to access. I have some basic outdated PHP/MySQL experience from years ago that I learned by reading on my own. With this I created a pretty complicated (for my skill set) application for tracking item/clothing donations by weight and date. That's the extent of my database training.

My current project: Tracking payroll expense by project.

My starting point is the Time Sheet template that comes from my version of Access. I assume 2016 as it comes with my Microsoft 365 subscription.



I needed to add the employees' pay rate to the time sheet, so I can calculate the cost per day/per project, but I don't want to have to enter it every time. So I added it to the employee list.

Then I updated the employee hours form so that it pulls the pay rate from each employee into the employee hours table.

No problem. The cost calculation works, except if I change the employee's pay rate, it doesn't just start with the next record. It changes ALL of them. Even the past ones.

Is there a way to pull the info in, but have the table save the value? Then when I update the rate in the employee table, only the new records in the employee hours table are affected.

[pbaldy]

This is similar to an invoicing app, where the solution is to save the selling price in the invoice details table. In your case, it sounds like your time sheet (wherever you have the hours). You can grab the pay rate and drop it in when you select employee:

http://www.baldyweb.com/Autofill.htm

In your case, the second method is appropriate.

[sharong582]

Thank you for this. It sounds like what I need. I'll try it next week when I'm back to work.

[pbaldy]

No problem, post back if you're stuck. Welcome to the site by the way!

[sharong582]

This method worked: https://www.youtube.com/watch?v=rSPS24EfijI

Perhaps easy stuff for an experienced programmer, but I have almost zero Access knowledge. I'm just going to describe it in brief in case someone else is looking for this solution.

This method auto fills the pay rate field based on my first selection (picking and employee) in my Work Hours form. It saves the data in the Work Hours table, so if I update the employee's rate in the employees table, it only affects new entries in my Work Hours table. If I go back and repick or change the employee in an already saved entry, it will change to the rate that is currently in the employees table based on my new choice. I am also able to manually edit the value if needed.

Thank you for the warm welcome.

[pbaldy]

Glad you got it working.