Linking fields from different tables (2003)

Topic

New Reply

Good morning

My database was quite simple, recording number of shifts worked on various contracts and totalling the costs.

My problem is that up until now I have populated the tables using a form. I enter the shift date in a text box, select the site the person worked at from a drop down box, the contract to which the selected site belongs is displayed in a text box, I select the individual’s name from a drop down box and finally select the individual’s job title from another drop down box (staff type). This sometimes changes for some individuals so has to be selected separately.

The cost of the shift is then displayed in another text box as a function of the staff type selected.

What’s changed is that we have been awarded a new contract with a whole set of different staff costs. So when entering a new shift worked on the new contract, the chosen staff type takes a figure from TblStaffType;StaffTypeCost that was true for previous contracts but not right for the new contract.

As there is no direct link between TblContract;ContractName and TblStaffType;StaffTypeCost in my database I wasn’t sure how to structure a link.

Could someone please give me a pointer on how to phrase a query calculated field to lookup the cost of the shift as a function of both the contract and the staff type. Or should I be looking to change the way in which the table relationships are structured?

Thanks

Regards

Graeme

Reply | Quote

Replies

Welcome back!

One option would be to add a whole new set of records to TblStaffType, with the new staff costs. You could use an indication in the staff type name to distinguish the new staff types from the old ones, for example an N at the end.
It’s a bit clunky but it wouldn’t require any redesign of the existing structure.

Another option would be to create new tables:

TblContractType:
– ContractTypeID (AutoNumber, primary key)
– ContractTypeName (string)

The first record in this table (with ContractTypeID = 1) would be the ‘old’ contract type, the second record (with ContractTypeID = 2) the ‘new’ type. You can add more types in the future.

TblStaffTypeCosts:
– ContractTypeID (number, part of composite primary key)
– StaffTypeID (number, part of composite primary key)
– StaffTypeCost (currency)

Create records for all StaffTypeIDs in tblStaffTypeCosts with ContractTypeID = 1 and the StaffTypeCost from TblStaffType. Then remove the StaffTypeCost field from TblStaffType.
Next, create records for all StaffTypeIDs in tblStaffTypeCosts with ContractTypeID = 2 and the new costs.

Add a number field ContractTypeID to tblContract and set its value to 1 for all contracts using the ‘old’ cost structure, and to 2 for the new contract using the ‘new’ cost structure.

You will have to build ContractTypeID into the queries and join the tables in the appropriate way. The calculations should then automatically use the appropriate cost structure.

This option is a lot more work, but in the end it is more flexible, because you can add other cost structures as needed.

Reply | Quote

Hans

Thanks for your reply.

I’m going to implement both options. Option 1 will give our commercial people what they want now. Option 2 is a better option long term and I can develop the database in conjunction with a separate need to monitor working hours rather than just shifts booked.

Thanks for the welcome back.

Regards

Graeme

Reply | Quote