Results 1 to 6 of 6
  1. #1
    MiaAccess is offline Advanced Beginner
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Texas
    Posts
    31

    relating various tables for payroll - Data from Quickbooks


    Tbljobs has a field PrevWageID, which will contain 1 of 4 prevailing wage types for this area, listed in the table on the left (2 columns).

    These are two tables which need to be related. However, there are different wages for each ID. I am just stumped as to relate these. The Job description ID (1st Column) is a field in the employee table; however, if it is a prevailing wage job, the 3 column is entered in QuickBooks, which assigns the prevailing wage rate. I am listing only one rate which is for Miami-Dade Heavy Construction. There are 3 more tables. I would set it up in QuickBooks to have the PwID MiaLight start with a 2, Broward with 3 and 4.
    ID Description JobDescIDF PWDescr PwIDMDH MDHRate MDHFringes
    1 MiaHeavy 1 Foreman >5 Empl 1F 23.98 6.02
    2 MiaLight 7 Oper. Eng Group V 1T 20.35 8.50
    3 BrowardHeavy 18 Oper. Eng Group III 1N 22.00 8.50
    4 BrowardLight 10 Oper. Eng Group IV 1O 21.44 8.50
    15 Laborer/Journeyman 1L 14.50 4.67
    16 Skill Labor w/tools 1S 15.00 4.67
    17 Carpenter/Journeyman 1C 22.20 6.02
    Last edited by June7; 11-12-2014 at 06:03 PM. Reason: typo

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I don't see a question. What is the issue?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    MiaAccess is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2011
    Location
    Texas
    Posts
    31
    How would I work it with the other 3 tables. I can see where I have to add a field in the Prevailing job description table.
    LocationID was missing which relates to one of the 4 items. Should I just use the same table add the other locations repeating the second table with the 7 earnings items, descriptions, PWID and rates. something like this. I thought perhaps there is a better way. Below sample for 2 types. Is that the way it is supposed to be?

    LocID JobDescIDF PWDescr PwIDMDH MDHRate MDHFringes
    1 1 Foreman >5 Empl 1F 23.98 6.02
    1 7 Oper. Eng Group V 1T 20.35 8.5
    1 18 Oper. Eng Group III 1N 22 8.5
    1 10 Oper. Eng Group IV 1O 21.44 8.5
    1 15 Laborer/Journeyman 1L 14.5 4.67
    1 16 Skill Labor w/tools 1S 15 4.67
    1 17 Carpenter/Journeyman 1C 22.2 6.02
    2 1 Foreman >5 Empl 2F 22.31 5.2
    2 7 Oper. Eng Group V 2T 19.98 7.5
    2 18 Oper. Eng Group III 2N 22 8.5
    2 10 Oper. Eng Group IV 2O 21.44 8.5
    2 15 Laborer/Journeyman 12 13.5 4.67
    2 16 Skill Labor w/tools 2S 14 4.67
    2 17 Carpenter/Journeyman 2C 20.58 6.52

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Why does the second Laborer/Journeyman have 12?

    I don't understand data structure and relationships. Looks like the LocID is repeated as a prefix for PwIDMDH. Why do that?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    MiaAccess is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Mar 2011
    Location
    Texas
    Posts
    31
    Yes, the relationship is my problem. I always thought there should be no repeats. I think this will work, but I don't think it is a good design.
    I have 7 job descriptions (and there could be more for a new job) and 4 different rates.

    The PwIDMDH is an earnings item which comes from QuickBooks and is assigned to the employee when they start working on a prevailing wage job, along with the rate. There is no other way in QuickBooks to pay a different rate than usual, unless there is a separate code for each earning item.

    QuickBook's reports are not good when it comes to construction jobs. We import the QuickBooks data into access and create a report which points out any errors in pay, workers comp rates, missing employees, or incorrect hours, like for example 38 hours of regular pay and 5 overtime.

    I am in the process of rewriting the database and do a better job than I did the last time.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I still don't know what the 'other 3 tables' are nor how anything is related.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Importing QuickBooks Tables: Sporadic ODBC Call failed Issue
    By HappyJohn in forum Import/Export Data
    Replies: 0
    Last Post: 10-08-2014, 03:08 PM
  2. Relating Multipe Tables With Similar Data To A Master Table
    By TotalChaos in forum Database Design
    Replies: 1
    Last Post: 04-13-2013, 12:57 AM
  3. Replies: 4
    Last Post: 03-12-2013, 01:59 PM
  4. Need help relating tables
    By LeahJB in forum Database Design
    Replies: 4
    Last Post: 02-15-2012, 08:37 AM
  5. Might someone help with relating some Tables?
    By djclntn in forum Database Design
    Replies: 20
    Last Post: 02-08-2012, 11:35 AM

Tags for this Thread

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