How do you set up multiple information for one field name like multiple rates of pay over time

[murphybodie]

I am new to data bases and have listened to numerous access tutorials. I can not figure out how to have a field called pay rate where you would enter a current pay rate but in 6 mos. the person gets a raise but where can I show the new rate without altering the beginning pay rate or without adding a new column . And, that would also apply to the date. Any help would be appreciated. Thank you.

[CJ_London]

where can I show the new rate without altering the beginning pay rate or without adding a new column

in a different table - something like

your existing table
tblEmployees
EmployeePK autonumber
EmployeeName text
.....
.....

the pay rates table

tblPayRates
PayRatePK autonumber
EmployeeFK long
PayRate currency
PayFrom date

your payrates would be in a subform on your employees form

you can use a relatively simple query to get the rate applicable on any given day

[jwhite]

Adding to what Ajax offered, I would add PayTo (expiry date for the row) to make it easier to get the current PayRate or find a PayRate in time:

Current PayRate:
SELECT PayRate FROM tblPayRates WHERE PayTo Is Null Or Date() Between PayFrom And PayTo

The first WHERE part would return the current PayRate that doesn't have an expired (PayTo) date.

imo, I'd use DateStart and DateEnd instead for the field names.

[CJ_London]

don't see why you need a date to - for the current rate

SELECT TOP 1 PayRate FROM tblPayRates WHERE EmployeeFK=[Enter employeePK] and PayFrom<=Date() ORDER BY PayFrom Desc

If you want rate for a specific date then change Date() to [Enter Date]

[murphybodie]

The reason I mentioned a date for current rate was meant to say that the date hired and at what rate was fine. My problem came in when a person gets a rate increase and the date of the increase. I didn't know how to show the new rate and date of increase.

[murphybodie]

Thank you. I will try that.