Perform Lookup in excel based on Access Criteria

[adam.grendell]

Hi all,

Not sure if this is the correct forum, but here goes.

I am creating a payroll db. The project is all but complete. The one thing I would like to do is find a way to perform data retrieval based on criteria. On the actual Payroll form, where the employer will figure up the check amount. I have a subform that pops up, giving the pay rate, marital status, and exemptions. I want Access to be able to automatically enter the correct Federal and State withholdings based on their Marital status and Exemptions. I have the tables from the IRS Publication 15. I just need a way to make Access read the data and enter the amount based on the criteria. I figured I could use a linked Excel table. I'm just not sure on how to make this work. It may be as simple as a macro, or it may be VBA, or even something else. I can't quite wrap my mind around the matter.

Any help would be greatly appreciated!

Thanks,

Adam

 

[adam.grendell]

bump........

 

[Beetle]

There's not really enough info in your post to provide any specific answer, but if this is a linked Excel table then you should be able to use any of the standard methods for data retrieval (query, DLookup, etc.).

 

[adam.grendell]

Thanks for the reply.

Here is the breakdown...

The data is the federal withholdings table. My lookup criteria are the amount of gross pay (ex. between 175-185) and the number of exemptions. I need this data to automatically retrieve the correct amount of withholding based on these two criteria.

A complete, real example would be:

If employee a makes $500 Gross pay, and has 2 exemptions, he should have $38 held out for taxes. I need the $38 to automatically be placed in the "Federal" textbox on my form. I know that it can be done, I'm just not really good with dlookup.

 

[adam.grendell]

Okay. I think I'm getting closer to what I need.
Something like this:

I have a table that has 5 columns: Marital Status, Exemptions, PayMin, PayMax, and Withholding.

I need to run a dlookup on this table based on the number of exemptions and marital status, as well as find the amount between the PayMin and PayMax fields to pull the Withholding field into a text box on my form.

I'm not good at dlookup, but maybe something like this:

Federal = Dlookup("Withholdings", "tblFedMarWeekly", (here's where I get lost) Exemptions = forms!frmEmployeesSub!Exemptions AND forms!frmPayroll!TotalGrs >= PayMin AND <=PayMax

Any help on composing this Dlookup???
I've tried looking up tools for this but nothing quite fit my problem.

Any help is greatly appreciated!

 

[Beetle]

It would look something like the following;

Dlookup("Withholding", "tblFedMarWeekly", "[Marital Status]= " & Forms!frmEmployeesSub![Marital Status] & " AND Exemptions = " & Forms!frmPayroll!Exemptions &
" AND PayMin <= " & Forms!frmPayroll!TotalGrs & " AND PayMax >= " & Forms!frmPayroll!TotalGrs)

Keep in mind that there may be slight changes necessary depending on where you are using this (i.e. in your forms code module or in the Control Source of a text box) and what the data types are of those fields in your forms record source (if any of them are text then some string delimiters will need to be added).

 

[adam.grendell]

After lots (an I mean LOTS) of trial and error to get this to work like I want, I think I need a new approach. I read online about using something called a recordset in VBA. Could I apply this since the data is in a table, and maybe use if/then statements for the rest? Thanks again for all the help!

 

[Pat Hartman]

Since the withholding tables are subject to change, it is necessary to store the amounts from the tax tables in the employee's payroll record. I would not use Excel to hold the withholding tables. I would import them into Access. Make sure that the correct primary key is defined. If the table doesn't have one, let Access assign an autonumber. You will also need to create create a unique compound index that includes the beginning and end of each range. This index will make the lookup more efficient.
The timing of when you would do this is unclear but you would do it with an update query. Create a query that joins the payroll table to the rates table. Change the query type to update. Choose the withholding amount from the employee table and set its value to the withholding amount from the rates table. Now switch the query to SQL view. You will NEVER be able to go back to Design view again for this query because we are going to change the join type to one that cannot be represented in Design view. Modify the join to be something like:
From tblEmployee Left Join tblRates On tblEmployee.Earnings >= tblRates.StartValue and tblEmployee.Earnings <= EndValue

If you don't import the rates table into Access, you will not be able to use this update query because Access requires an updateable recordset for this action and Excel tables cannot be updated and so a join that includes one will not be updateable.