Lookup criteria

[cherosoullis]

Hello,

I have one table with employees and positions. Each position has a specific rate which is stored in another table (table2). Since the rates of the positions are changing, I want each time the employee get paid with the correct rate.
The problem is that I do not want to lose historic data since I want the rate to calculate the salary. I thought that the best way is to look up for the rate according to the position and the period that is valid.

I am trying days now and I cannot figure out how to make this happen.

Can you please help me.

I have attached herewith a small examble with the two table that I want to use the relationship.

 

[YNWA]

You need to perform the calculations in a query. Do not store calculations.

Your relationships are wrong. Have the relationships between the 2 IDs.

You need to set the Rate field in Table1 to RateID and store this as a Number.

Then your relationship will be RateID in Table1 to ID in Table2.

You then build a query to pull the fileds you want. If you want the Rate amount that person is on, pull Rate from Table2 into the query. The relationship from RateID to ID will allow you to show the values.

 

[YNWA]

If you then want to calculate a salary based on this rate, you perform the calcs in the query.

Eg Salary: Table2.[Rate]*150 will give you a rate for 20 working days work. = 787.5 for a Rate of 5.25.

My SQL for this is:

SELECT Table1.ID, Table1.Name, Table1.Date, Table1.Position, Table2.Rate, [Table2].[Rate]*150 AS Salary
FROM Table1 INNER JOIN Table2 ON Table1.RateID = Table2.ID;

 

[cherosoullis]

I don't this that this is what I want.

There will be many rates for the same position but on different dates.
What I want is the rate that much with the position and the date period that is valid.

In your exambel you don't mention the dates at all.