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!