Lookup fields using two pairs of fields

[Nicnacs]

Hello

I'm trying to create a timesheet system. I have one table that is where time data is stored for each day for each person (one record for each day for each person). I have a table where information on each member of staff is stored (Name, how much holiday they get, how many hours they work each week) and I'm trying to create a table that pulls the information together for each week.

Each member of staff has a unique ID and I'm using the week numbers to identify weeks. The time data table works fine. I can enter a week's worth of data for myself for instance and it calculates how much work I've done, how much overtime or not, how many days annual leave or sick, etc.

I thought if I defined a relationship where the Summary table was related to the Time Data table by both Staff ID and Week Number, I'd be OK. So in the Summary Table there's a field defined as Hours Worked and it's a lookup field that should go to the Time Data table and, when the Staff ID AND Week Number matches, look up the right value.

But it isn't. It's looking up a value for everyone for all weeks. So in week 25, I've worked over 100 hours!

What am I doing wrong? Should this not work as a concept? If you search in the Time Data layout for my Staff ID and Week 25, it pulls up 5 records and totals the hours worked and so on perfectly. Why don't these values then translate over to the Summary Table?

I am perplexed!

Thanks