Results 1 to 7 of 7
  1. #1
    agrendell is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Location
    Newport, AR
    Posts
    6

    Trying to lookup data from one table based on data from another


    Hello all.

    I am trying to create a Payroll program for my boss. I have the general program finished, and it works quite well. I'm still making the look of it better, but it's getting there. But I'm attempting to solve the last headache, which is making it automatically look up tax withholdings based on the employee's marital status, number of exemptions, and their gross pay. Sure, the boss could easily look up the withholdings and enter them manually but that defeats the entire purpose of what they are wanting. I'm almost 100% sure this is a query issue that I just cant wrap my head around. I know what I'm wanting it to look up, I just don't know how to make Access do it.

    Here are the tables and forms I'm using to try to make this work: I have a tblEmployeeInfo that holds the employee's marital status and exemptions, and I have a tblPayroll that holds pay records, and is where the pay rate, gross pay, etc. is figured. I also have a tblStateTax that has the entire weekly withholding table in it, that I imported from an excel spreadsheet and created an Access table with the data. the fields, going left to right, are "At least", "But Less Than", "0 Exemptions", "1 Exemption", and so on.

    All the information is there, I just need to know how to make Access go and find the proper "cell" in the StateTax table that is based on the Gross Pay falling between the At Least and But Less Than fields, and the number of exemptions.

    Can this be something like an Autofill in access, or does it need to be a query that I execute? I'm a bit in over my head with this last piece and any help at all would be appreciated.

    Thank you all in advance!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Personally, I would not build this database. Labor and tax laws are too complex. Why reinvent the wheel? An economical alternative would be OTS program like QuickBooks.

    Most anything can be done with enough code. So, a query might get what you need, or maybe a DLookup(). Without a better understanding of data structure and processes, hard to advise specifics.
    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
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You are correct, it is a query.
    Attached is the image of :
    the result client-Gross amts
    the tax table, and how to build the query using the range in the tax table.

    Click image for larger version. 

Name:	tax rate table query.png 
Views:	17 
Size:	54.0 KB 
ID:	35786

  4. #4
    agrendell is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Location
    Newport, AR
    Posts
    6
    June7, I agree that its a bit of a silly idea when Quickbooks or Peachtree can do the same thing. Downside is my boss asked me to, and they don't really want to pay for the software. Since I'm an IT Admin for my job, they asked me to do it since it (kind of) falls under my job duties. Never been much of a programmer, and Access allows me to do most of what I want without knowing tons and tons of VBA.

    ranman256, I will take a look at this when I get home tonight and see if I can apply it to my db and make this work. Thank you so much for the information! I'll reply back with an update afterwards.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Boss doesn't want to buy software but willing to spend what is likely much more in payroll cost on this effort for you to develop a program that might actually subject the company to greater liability risk if labor and tax laws are not adequately addressed?
    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.

  6. #6
    agrendell is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2015
    Location
    Newport, AR
    Posts
    6
    I understand where you are coming from and I respect your opinion. The basis of this program is basically a way to print checks and get away from handwriting them. It's a small business and funds are saved whenever possible. We have a 3rd party accountant that handles all taxes for the company. This is just something to speed up the process of making checks for the employees and sending the withholdings to the accountant. Outside of that, there isn't any labor or tax laws that are being ignored. Thank you for your input though. I can see where someone who wasn't standing on my side of the fence might see things that way.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Your program is calculating the payroll and takes into account overtime labor laws? Or is overtime never a concern? You keep an eagle eye on changing tax laws and modify tax withholding table every year? Your program can replicate reports for any period regardless of changes?

    Ranman's schema looks good and hope it provides the guidance for your situation.

    Good luck with your project.
    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. Replies: 2
    Last Post: 02-21-2018, 01:32 AM
  2. Replies: 9
    Last Post: 08-11-2016, 11:30 AM
  3. Replies: 3
    Last Post: 12-01-2015, 12:20 PM
  4. Replies: 1
    Last Post: 05-31-2015, 09:20 AM
  5. Accessing Data from a Lookup Table
    By TastyMouse in forum Queries
    Replies: 3
    Last Post: 05-08-2013, 11:26 PM

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