Results 1 to 7 of 7
  1. #1
    Podder is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22

    New Rates after New Contract

    Hi,
    I was wondering if someone could help me with the following senario, I have a database with these tables:
    TaskTBL (TaskID(Primary Key), Task, PayID) where PayID is a code used to determine the rate for each Task
    PayTBL (PayID (Primary Key), PayNo, Rate)
    ContractTBL (ContractID (Primary Key), ContractStartDate, ContractDuration)

    The TaskTBL is related to the PayTBL (PayID) - There are about 10 different Tasks and PayID's

    What I am trying to do is, when the new contract starts, what is the best way to add the new Rates?
    I can currently add the same Tasks to the TaskTBL under new ID's and add the New PayID to these and link these by creating a new table (ContractPayTBL) where each PayID is given a new ContractID, is this the correct way to achieve this problem or is there a better way?

    Example:
    TaskTBL
    TaskID Task PayID
    Task1 Work1 1
    Task2 Work2 2

    PayTBL
    PayID PayNo Rate
    1 1 $20
    2 2 $30

    ContractPayTBL
    ContractPayID ContractID PayID
    1 1 1 (this would go to 10)


    11 2 11 (this would go to 20)

    Thanks

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    from your description the pay rates are the same, regardless of task - you just choose one of them. Or are you saying a contract always has 10 tasks and the rate for each task can vary from contract to contract.

    You say you have about 10 different tasks and payID's but your contract paytable is showing PayID's from 11-20

    I just don't see how the one relationship you mentions works. Further clarification required

  3. #3
    Podder is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22
    Quote Originally Posted by CJ_London View Post
    from your description the pay rates are the same, regardless of task - you just choose one of them. Or are you saying a contract always has 10 tasks and the rate for each task can vary from contract to contract.

    You say you have about 10 different tasks and payID's but your contract paytable is showing PayID's from 11-20

    I just don't see how the one relationship you mentions works. Further clarification required
    Sorry for the poor explanation.
    pay rates are different, they vary with each new contract.
    The 11-20 was what I was looking at doing, so currently there are 1-10, to add new ones I was looking at adding the 11-20 with new rates

  4. #4
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Quote Originally Posted by Podder View Post
    pay rates are different, they vary with each new contract.
    So you need to store the rate in the ContractPayTBL and not in the PayTBL.
    Groeten,

    Peter

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so if pay rates can change from contract to contract, why do you need a pay table? I would have thought your structure would be something like

    tblContracts
    ContractPK
    ContractName

    tblTasks
    TaskPK
    TaskName

    tblContractTasks
    ContractTaskPK
    ContractFK
    TaskFK
    PayAmount

  6. #6
    Podder is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22
    Quote Originally Posted by xps35 View Post
    So you need to store the rate in the ContractPayTBL and not in the PayTBL.
    so your saying my PayTBL, should just have PayID and PayNo
    then in the ContractpayTBL I have ContractPayID ContractID PayID and Rate?

  7. #7
    Podder is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2018
    Posts
    22
    Quote Originally Posted by CJ_London View Post
    so if pay rates can change from contract to contract, why do you need a pay table? I would have thought your structure would be something like

    tblContracts
    ContractPK
    ContractName

    tblTasks
    TaskPK
    TaskName

    tblContractTasks
    ContractTaskPK
    ContractFK
    TaskFK
    PayAmount
    thanks London, I’ll try that

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Get last 3 rates per well
    By wsastroredjo in forum Queries
    Replies: 1
    Last Post: 04-12-2017, 08:23 AM
  2. rental contract database
    By chermiti in forum Access
    Replies: 11
    Last Post: 03-15-2015, 10:58 PM
  3. How to work out commission rates
    By matpaulin in forum Queries
    Replies: 1
    Last Post: 02-07-2013, 06:15 PM
  4. Contract Management project
    By TheEngineer in forum Access
    Replies: 4
    Last Post: 07-16-2010, 02:57 PM
  5. Creating contract through Access
    By Alexandre Cote in forum Reports
    Replies: 5
    Last Post: 07-16-2010, 08:13 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums