Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2008
    Location
    Medway, Kent, United Kingdom
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking fields from different tables (2003)

    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
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking fields from different tables (2003)

    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.

    <img src=/w3timages/blackline.gif width=33% height=2>

    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.

  3. #3
    New Lounger
    Join Date
    Sep 2008
    Location
    Medway, Kent, United Kingdom
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking fields from different tables (2003)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •