PAYROLL PUZZLE - LOOKUP WITHHOLDING TAXES

[Sammie]

I am trying to look up Federal and State withholding taxes based on gross
wage. I have the following tables and fields:

tblEmployees: EmployeeID, EmployeeName
tblSueFed: FedID, FedTax, AtLeast, LessThan
tblSueState: StateID, StateTax, AtLeast, LessThan
tblBobFed: FedID, FedTax, AtLeast, LessThan
tblBobState: StateID, StateTax, AtLeast, LessThan
tblPaychecks: CheckID, EmployeeID, GrossWage

One employee is named Sue, and one is Bob. Each employee will have their
own 2 tax tables, one for Federal and one for State.

[AtLeast] and [LessThan] are currency values, minimum & maximum for the
FedTax figure.

I can’t figure out how to relate the EmployeeID to the appropriate two tax
tables for the lookup.

For each paycheck to Sue, the gross wage needs to look up the FedTax from
the SueFed table and StateTax from the SueState table, based on the gross
wage being greater than or equal to the [AtLeast] value and less than the
[LessThan] value.

Similarly, for each paycheck to Bob, the gross wage needs to look up the
FedTax from the BobFed table and StateTax from the BobState table, based on
the gross wage being greater than or equal to the [AtLeast] value and less
than the [LessThan] value. Etc.

Can anyone help me create the queries using Access 2003?
Thanks
Sammie

 

[Ron]

I am just curious as to why you don't just have one Fed tax Table and one
State table then Have a payroll record for Sue with a field Fed, which was
lookedup in the fedTax table, a field State, which was lookedup in the state
Tax Field, a medicare field, which is a computed field, and other fields for
deductions., a field for her gross, and the date of the check. Now to write
her check you just query for the date of the check, The net pay is compute
based on everything in the record for Sue and that date. I am running
payroll now with this system and all I do is enter the gross wages.
Everything else is lookedup and computed. I wrote it in access95 and updated
it from episode to episode of Access.

 

[John W. Vinson]

I am trying to look up Federal and State withholding taxes based on gross
wage. I have the following tables and fields:

tblEmployees: EmployeeID, EmployeeName
tblSueFed: FedID, FedTax, AtLeast, LessThan
tblSueState: StateID, StateTax, AtLeast, LessThan
tblBobFed: FedID, FedTax, AtLeast, LessThan
tblBobState: StateID, StateTax, AtLeast, LessThan
tblPaychecks: CheckID, EmployeeID, GrossWage

One employee is named Sue, and one is Bob. Each employee will have their
own 2 tax tables, one for Federal and one for State.

[AtLeast] and [LessThan] are currency values, minimum & maximum for the
FedTax figure.

I can’t figure out how to relate the EmployeeID to the appropriate two tax
tables for the lookup.

For each paycheck to Sue, the gross wage needs to look up the FedTax from
the SueFed table and StateTax from the SueState table, based on the gross
wage being greater than or equal to the [AtLeast] value and less than the
[LessThan] value.

Similarly, for each paycheck to Bob, the gross wage needs to look up the
FedTax from the BobFed table and StateTax from the BobState table, based on
the gross wage being greater than or equal to the [AtLeast] value and less
than the [LessThan] value. Etc.

Can anyone help me create the queries using Access 2003?
Thanks
Sammie

If you have a table for each employee, your table design IS SIMPLY WRONG. You
should have *one* Federal and *one* state tax rate table, with whatever other
informations (deductions, e.g.) affect the witholding rate - *not* a separate
table for each employee. What you're proposing would be a monstrous
maintenance nightmare every time an employee is hired, leaves, or changes
status!

To directly answer your question, you can use a "Non Equi Join" - this must be
done in SQL not the query grid. Assuming a table tblPaychecks and a table
tblFed, a portion of the query might be

FROM tblPaychecks INNER JOIN tblFed
ON tblPaychecks.GrossWage >= tblPaychecks.At Least
AND tblPaychecks.GrossWage < tblPaychecks.LessThan


John W. Vinson [MVP]