Access query

[Christina]

I am creating a database to input hours worked, and calculating pay. I want
to create a field which will calculate the income tax and social security
payable. Eg if earns between 150 to 180 a person pays $10. etc. Pleas e
help. I am basic in my knowledge of access. no SQL knowledge.

Thanks

 

[Clifford Bass]

Hi Christina,

Since you say "social security" I presume this is an employer in the
USA. There is no simple "if earns between 150 to 180 a person pays $10".
For some it could be nothing. For others it could be $50. How much do you
know about withholding from the employer's point of view? If not much, that
is where you need to start; learning the convolutions of what an employer has
to do to abide by the tax rules, taking into account the information supplied
by the employees on their W-4 forms, the type of pay period and the
frequently changing tax rate rules. This applies to all of the federal,
state and local levels. You will also have to deal with pre-tax deductions
such as health insurance or retirement contributions. And there may be
post-tax deductions. Once you have a good grasp on that, then you will need
to decide if you are going to use tax tables or stick entirely to formulae,
where allowed. Note that these usually change every year. So you will need
to plan for that also. Get publication 501 from the IRS for a lot of that
information at the federal level. Likewise check with your state and local
taxing authorities for their publications. Once you have learned all that
and decided which direction to go, you can set up your tables. Then post
back with more specifics about your system if you have specific questions.

To learn more about Access one thing to try is an internet search for
"access tutorial". Likewise "sql tutorial" for help learning SQL. Others
may have some good specific site recommendations.

Good Luck!

Clifford Bass

 

[Christina]

I am not in the USA, and our table and law is pretty simple and straight
forward. Both tables have three columns.
EGif you earn between

100 105 you pay 1.00
105.01 109 you pay 2.00

Is there a simple way to do that?

Thanks

 

[Clifford Bass]

Hi Christina,

Oh, for such a simple tax code here!!!

The answer is yes. If doing it on a form, in order to populate a tax
withheld field, you could use an after update event on the hours worked
field. I am guessing you already have one in order to calculate the total
pay anyway. So add to that something like:

[txtTaxesFieldOnForm] = DLookup("[you pay field]", "[tax table]",
[txtTotalPayFieldOnForm] & " between [minimum pay] and [maximum pay]")

If that does not get you where you need, post back with more specifics
on exactly what you are doing: table, field and form names along with the
code that is not working.

Clifford Bass

 

[Christina]

Thanks for replying. I will be trying it out in the morning. However, to
let you know what I am doing:
I have a form that I input the hours worked and other relevant data for the
pay period.
I then run a query that calculates the pay, overtime , and net pay after
deduction. That query updates a table which I call Salaries Register.
I would like to include in that query the calculations for Income tax and
Social Security.
Thanks so much.
Good night

Hi Christina,

Oh, for such a simple tax code here!!!

The answer is yes. If doing it on a form, in order to populate a tax
withheld field, you could use an after update event on the hours worked
field. I am guessing you already have one in order to calculate the total
pay anyway. So add to that something like:

[txtTaxesFieldOnForm] = DLookup("[you pay field]", "[tax table]",
[txtTotalPayFieldOnForm] & " between [minimum pay] and [maximum pay]")

If that does not get you where you need, post back with more specifics
on exactly what you are doing: table, field and form names along with the
code that is not working.

Clifford Bass

I am not in the USA, and our table and law is pretty simple and straight
forward. Both tables have three columns.
EGif you earn between

100 105 you pay 1.00
105.01 109 you pay 2.00

Is there a simple way to do that?

Thanks

 

[Clifford Bass]

Hi Christina,

Generally, at least for me, I like to do all the calculations on the
form. Then the user can see the results right away. But that is my personal
preference.

To do it using queries you may have to do more than one query because
you probably will need to have the calculated gross pay before you can do the
taxes part. To do an update query to compute the taxes, start a new query
and add the Salaries Register table, the Income Tax table and the Social
Security table to it. Close the add table window. Drag the fields you want
to update to the lower part of the window along with the gross pay. In the
criteria line for the gross pay, enter something like "between [Income Tax
Minimum] and [Income Tax Maximum] and between [Social Security Minimum] and
[Social Security Maximum]". That hopefully will get it so it picks the
correct single row from each of the tax tables. If you have trouble, go into
the query's SQL view and post your SQL.

Clifford Bass

P.S. What country?

 

[Christina]

I am in Belize, that small English speaking country between Guatemala,
Mexico, and Honduras.

I like the idea of the calculation for the tax and social security on the
form. But how do I have it calculate 'after update' Sorry, I need step by
step instructions for this. I have the ITax and Soc Sec tables. I was
trying to do it in a query. I brought the table with the gross pay, and the
income tax table. Do have to join any fields. Dont really know how to
proceed.

Please bear with me.

Thanks

Thanks for replying. I will be trying it out in the morning. However, to
let you know what I am doing:
I have a form that I input the hours worked and other relevant data for the
pay period.
I then run a query that calculates the pay, overtime , and net pay after
deduction. That query updates a table which I call Salaries Register.
I would like to include in that query the calculations for Income tax and
Social Security.
Thanks so much.
Good night

Hi Christina,

Oh, for such a simple tax code here!!!

The answer is yes. If doing it on a form, in order to populate a tax
withheld field, you could use an after update event on the hours worked
field. I am guessing you already have one in order to calculate the total
pay anyway. So add to that something like:

[txtTaxesFieldOnForm] = DLookup("[you pay field]", "[tax table]",
[txtTotalPayFieldOnForm] & " between [minimum pay] and [maximum pay]")

If that does not get you where you need, post back with more specifics
on exactly what you are doing: table, field and form names along with the
code that is not working.

Clifford Bass

I am not in the USA, and our table and law is pretty simple and straight
forward. Both tables have three columns.
EGif you earn between

100 105 you pay 1.00
105.01 109 you pay 2.00

Is there a simple way to do that?

Thanks

 

[Clifford Bass]

Hi Christina,

Sounds like a good place to be!

To do the computations on the form do something like this (you will
need to adjust for your actual fields and data): Right-click on the regular
hours worked text box and choose Properties. Go to the Events tab and click
in the After Update event line. You will get a down arrow and a button with
three periods. Click on the button and choose Code Builder. Because you
will probably need to call the same code from several after update events,
such as also on an overtime hours worked field, in this one's event just call
out to another function that you will write shortly. So a single line:

CalculateGrossPayTaxesNetPay

Repeat the process for the overtime hours text box.

Then in the code window at the bottom create the new subroutine to do
the calculations:

Private Sub CalculateGrossPayTaxesNetPay()

[txtRegularPay] = Nz([txtRegularHoursWorked], 0) *
[txtThisPersonsRegularPayRate]
[txtOvertimePay] = Nz([txtOvertimeHoursWorked], 0) *
[txtThisPersonsOvertimePayRate]
[txtGrossPay] = [txtRegularPay] + [txtOvertimePay]
[txtIncomeTaxes] = DLookup("TaxAmount", "IncomeTaxTable", [txtGrossPay]
& " between [PayRangeLow] and [PayRangeHigh]")
[txtSocialSecurityTaxes] = DLookup("TaxAmount",
"SocialSecurityTaxTable", [txtGrossPay] & " between [PayRangeLow] and
[PayRangeHigh]")
[txtNetPay] = [txtGrossPay] - [txtIncomeTaxes] - [txtSocialSecurityTaxes]

End Sub

This assumes that the regular pay and overtime pay rates for the person
are available in the form. If not, you could use the DLookup function to get
them using the person's ID number.


If doing it in the queries, you do not need to join the fields between
the main hours worked/pay table and the tax tables because you are "joining"
them with the conditions you enter in the criteria line.

Hope that makes sense,

Clifford Bass

 

[Christina]

Hi,
Thanks for your reply. I tried for hours and still can't get this part
to work. I've finished the form, with the calculation for the gross pay.
Can I have something that works just with the gross pay field to calculate
the income tax and social security payment . I would prefer to have it
calculate on the form, if I can handle it.
If it would not be too much to ask, I can send you the database. It is
small.

Sincerely,

Cristina

Hi Christina,

Sounds like a good place to be!

To do the computations on the form do something like this (you will
need to adjust for your actual fields and data): Right-click on the regular
hours worked text box and choose Properties. Go to the Events tab and click
in the After Update event line. You will get a down arrow and a button with
three periods. Click on the button and choose Code Builder. Because you
will probably need to call the same code from several after update events,
such as also on an overtime hours worked field, in this one's event just call
out to another function that you will write shortly. So a single line:

CalculateGrossPayTaxesNetPay

Repeat the process for the overtime hours text box.

Then in the code window at the bottom create the new subroutine to do
the calculations:

Private Sub CalculateGrossPayTaxesNetPay()

[txtRegularPay] = Nz([txtRegularHoursWorked], 0) *
[txtThisPersonsRegularPayRate]
[txtOvertimePay] = Nz([txtOvertimeHoursWorked], 0) *
[txtThisPersonsOvertimePayRate]
[txtGrossPay] = [txtRegularPay] + [txtOvertimePay]
[txtIncomeTaxes] = DLookup("TaxAmount", "IncomeTaxTable", [txtGrossPay]
& " between [PayRangeLow] and [PayRangeHigh]")
[txtSocialSecurityTaxes] = DLookup("TaxAmount",
"SocialSecurityTaxTable", [txtGrossPay] & " between [PayRangeLow] and
[PayRangeHigh]")
[txtNetPay] = [txtGrossPay] - [txtIncomeTaxes] - [txtSocialSecurityTaxes]

End Sub

This assumes that the regular pay and overtime pay rates for the person
are available in the form. If not, you could use the DLookup function to get
them using the person's ID number.


If doing it in the queries, you do not need to join the fields between
the main hours worked/pay table and the tax tables because you are "joining"
them with the conditions you enter in the criteria line.

Hope that makes sense,

Clifford Bass

I am in Belize, that small English speaking country between Guatemala,
Mexico, and Honduras.

I like the idea of the calculation for the tax and social security on the
form. But how do I have it calculate 'after update' Sorry, I need step by
step instructions for this. I have the ITax and Soc Sec tables. I was
trying to do it in a query. I brought the table with the gross pay, and the
income tax table. Do have to join any fields. Dont really know how to
proceed.

Please bear with me.

Thanks

 

[Christina]

I dont know if I sen this already. Apologies if I did.
I tried, and have not been able to do this. I still have the query which
calculates the pay and gross pay. I went ahead and did the calculations on
the form also, so the gross pay is calculated on the form. Would it be too
much to ask if I can send you the database, it is small. I would prefer to
calculate the tax and SS deductions on the form also.

Thanks,
Cristina

I am in Belize, that small English speaking country between Guatemala,
Mexico, and Honduras.

I like the idea of the calculation for the tax and social security on the
form. But how do I have it calculate 'after update' Sorry, I need step by
step instructions for this. I have the ITax and Soc Sec tables. I was
trying to do it in a query. I brought the table with the gross pay, and the
income tax table. Do have to join any fields. Dont really know how to
proceed.

Please bear with me.

Thanks

Thanks for replying. I will be trying it out in the morning. However, to
let you know what I am doing:
I have a form that I input the hours worked and other relevant data for the
pay period.
I then run a query that calculates the pay, overtime , and net pay after
deduction. That query updates a table which I call Salaries Register.
I would like to include in that query the calculations for Income tax and
Social Security.
Thanks so much.
Good night

Hi Christina,

Oh, for such a simple tax code here!!!

The answer is yes. If doing it on a form, in order to populate a tax
withheld field, you could use an after update event on the hours worked
field. I am guessing you already have one in order to calculate the total
pay anyway. So add to that something like:

[txtTaxesFieldOnForm] = DLookup("[you pay field]", "[tax table]",
[txtTotalPayFieldOnForm] & " between [minimum pay] and [maximum pay]")

If that does not get you where you need, post back with more specifics
on exactly what you are doing: table, field and form names along with the
code that is not working.

Clifford Bass

:

I am not in the USA, and our table and law is pretty simple and straight
forward. Both tables have three columns.
EGif you earn between

100 105 you pay 1.00
105.01 109 you pay 2.00

Is there a simple way to do that?

Thanks

 

[Christina]

I tried and tried but still have not been able to get this. I've updated the
form to calculate the gross pay. Can you please tell me exactly what should
be in the field to read from the Tax table and social security table./ The
table fields are minimum , maximum, you pay. Table names are Income Tax and
Social Security.


Thanks

I am in Belize, that small English speaking country between Guatemala,
Mexico, and Honduras.

I like the idea of the calculation for the tax and social security on the
form. But how do I have it calculate 'after update' Sorry, I need step by
step instructions for this. I have the ITax and Soc Sec tables. I was
trying to do it in a query. I brought the table with the gross pay, and the
income tax table. Do have to join any fields. Dont really know how to
proceed.

Please bear with me.

Thanks

Thanks for replying. I will be trying it out in the morning. However, to
let you know what I am doing:
I have a form that I input the hours worked and other relevant data for the
pay period.
I then run a query that calculates the pay, overtime , and net pay after
deduction. That query updates a table which I call Salaries Register.
I would like to include in that query the calculations for Income tax and
Social Security.
Thanks so much.
Good night

Hi Christina,

Oh, for such a simple tax code here!!!

The answer is yes. If doing it on a form, in order to populate a tax
withheld field, you could use an after update event on the hours worked
field. I am guessing you already have one in order to calculate the total
pay anyway. So add to that something like:

[txtTaxesFieldOnForm] = DLookup("[you pay field]", "[tax table]",
[txtTotalPayFieldOnForm] & " between [minimum pay] and [maximum pay]")

If that does not get you where you need, post back with more specifics
on exactly what you are doing: table, field and form names along with the
code that is not working.

Clifford Bass

:

I am not in the USA, and our table and law is pretty simple and straight
forward. Both tables have three columns.
EGif you earn between

100 105 you pay 1.00
105.01 109 you pay 2.00

Is there a simple way to do that?

Thanks

 

[Clifford Bass]

Hi Christina,

In case you see this; I think you are almost there! Post your form's
code and I expect I can tell you what needs to be tweaked/done to get the
rest to work.

Clifford Bass

 

[Christina]

Hi Clifford,
I know I am missing something. I have not used Dlookup function before. so
maybe I need more detail instructions.

This is what I did. This is the code in the Income Tax field on the form
=DLookUp("[TaxAmount ]","[IncomeTax]",[txtGrossPayFieldOnForm] & " between
[PayRangeLow] and [PayRangeHigh]")


This is how the Income Tax Table looks. Table name is IncomeTax. Data in
table for now is dummy.

PayRangeLow PayRangeHigh TaxAmount
$800.00 $899.00 $9.55

when I follow the procedure and click on the code builder, there was
already some stuff in there. I added the line, so it now looks like this

Option Compare Database

Private Sub Add_New_Record_Click()
On Error GoTo Err_Add_New_Record_Click


CalculateGrossPayTaxesNetPay


DoCmd.GoToRecord , , acNewRec

Exit_Add_New_Record_Click:
Exit Sub

Err_Add_New_Record_Click:
MsgBox Err.Description
Resume Exit_Add_New_Record_Click




End Sub

in the bottom half, again there was already some stuff there, it now looks
like this:
Private Sub CalculateGrossPayTaxesNetPay()


[txtIncomeTaxes] = DLookup("TaxAmount", "IncomeTaxTable", [txtGrossPay]
& " between [PayRangeLow] and [PayRangeHigh]"


Private Sub Text66_AfterUpdate()

End Sub

I am waiting for your help.

Thanks

 

[Christina]

I was trying to follow your instructions in putting the code to calculate
the social security and income tax. But I am lost. Would you considering me
emailing you the database. It is small.


Would greatly appreciate your help
Thanks


Cristina

 

[John W. Vinson]

I was trying to follow your instructions in putting the code to calculate
the social security and income tax. But I am lost. Would you considering me
emailing you the database. It is small.


Would greatly appreciate your help
Thanks


Cristina

Christina, there are hundreds of posts here every day. You're not the only
one.

Starting a new thread rather than replying to an existing thread will make it
all but impossible for the person you think you're talking to to notice.
Please bring up the previous thread and Reply, probably with the person's name
and some of the "instructions" that you're trying to follow.