Microsoft Access 2013 Get Cost using Hourly Rate Using Based on Entry Date

I have a two tables.  tblsalposact shows EmpName and a list of Effective Date Salary went into affect and the hourly rate. Tbltimesheet shows EmpName Actual time and the Date of Entry. A person making $1.00 an hour makes an entry in tbltimesheet on 5/1/ for 60 minutes. Cost will be $60.00.  If the hourly rate changed to $1.10 an hour  for that person with an Effective Date Salary was entered on 5/2/2015 and that person makes an entry in tbltimesheet on 5/2 for 60 minutes cost would be $66.  In addition sometimes a salary change would happen after a person makes an entry so I guess I would have to trigger an effect after hourly rate is changed from another part of the database. So what would my query looklike to reflect the multiple salary changes? Thanks for the help. FIle is attached.
CostEffectiveDate.accdb
marlind605Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
This is a lot more than a single question with a single answer.
For example:

Is the user entering the start and end times, ...or the minutes, ...or the actual time?
In most cases, the start and end time are entered and the Minutes/Actual Times are "Calculated" (not stored in the table as you have )

The common field should be some sort sort of EmployeeID, (number), and not the name (text),
(a name can theoretically be duplicated)

Finally, to me, you would need a "Rate History" table as well, ...that would store a users rates over time.
This would make calculating total amounts easier.
marlind605Author Commented:
Is the user entering the start and end times, ...or the minutes, ...or the actual time?
 In most cases, the start and end time are entered and the Minutes/Actual Times are "Calculated" (not stored in the table as you have )
The user enters the start and stop times. Sometimes the user enters it manually.
The tblsalposact is the rate history. It shows the name as well as the hourly rate and the effective date of the salary. A record could be entered on the timesheet today and the database would be informed of a hourly rate change the next day. I need to update the Cost in the tbltimesheet.
marlind605Author Commented:
Any other thoughts?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

marlind605Author Commented:
How do I get it to find the correct rate for the date of entry. tblsalposact shows salary history. An administrator may change a salary today but the salary goes into effect on May 1. All date of entry made on May 1 and after will need to reflect the new rate. Does this help?
marlind605Author Commented:
Ended up doing 2 update queries. Where my Date of Entry was greater then the accepted date. I used one query to clear the data then updated the effective date with the new effective date and the date of entry where the new effective date is > the previous date.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marlind605Author Commented:
Spent a lot of time trying to get the right answer but finally got it using logic.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.