Thanks for such a quick response!
The fields in the Travels table match what I currently have in my events file.
The fields in the REimb table include an Employee field (which currently resides in the other table) and a mileage field. That mileage field shouldn’t be total miles driven (100 miles), but should be the IRS reimbursement amount per mile (i.e. $0.54 per mile).
I’m trying to relate the current reimbursement amount ($0.54 per mile) to the event start date, so that it will calculate the total reimbursement amount in the event table with the appropriate amount per mile (100 miles at $0.54). Sorry if I didn't explain that well.
change the name of the Mileage field to Reimbursement and change its formula to Sum(Travels::Mileage) * .54
Hmmmmm. . .
I need the Reimbursement table to include multiple calendar ranges for IRSMileage Reimbursement. So the records would be like:
1/1/15 to 6/30/15 at $0.55/mile
7/1/15 to 12/31/15 at $0.575/mile
1/1/16 to 12/31/16 at $0.54/mile
I don't think it can be a global field.
Based on the event start date, I need to find out what the IRS mileage reimbursement rate is during the appropriate date range it falls into. The event file has multiple years of data. I want the mileage reimbursement calculation (miles X rate) to remain static in the events file. Right now I update that reimbursement rate as the IRS changes it, but it changes past records as well.
The events file has only one record per employee that contains the total mileage.
That changes it all
Thank you so much! That did it!
I knew that I somehow had to make those two dates relate but did not know how to write the relationship. Your example really helped! I implemented the changes in my two tables and tested the data, and all worked fine.