Getting the right field for calculation

[Guest]

Thank you for your time.

Table tblWTAX is like this:

BirStatus Field1 Field2 Field3 Field4 Field5 Field6 Field7
% .5 .10 .20 .25 .30 .40 .50
constnt 50.00 70.00 90.00 110.00 104.17 150.50 175.98
S 100 1500 2700 3400 5600 7833 9000
ME2 100 2300 3500 4000 5600 7823 15000

Table tblEmployeeMaster like this:
EmpIDno (Index)
BIRStatus (Relation with tblWTAX)

Problem:

Ex. txtgrossfortax = 6000

check tblEmployeeMaster table for employee BIRStatus. If BIRStatus = ME2
the amount of txtgrossfortax (which is 6000 should get the value of Field5
which is 5600 & % of .30

compute txtgrossfortax less value of Field5 (which is 4917) = 400

then multiply 400 by 30% = 120 (20% is constant based on tblWTAX Field5) = 120
Add 104.17 to 120 = 224.17 (104.17 is constant based on tblWTAX Field)

224.17 should be the tax that will go to the txtWTAX

PLease help me with the syntax.

Thank you very much.

Hope to hear from you soon.

Lance Arnold

 

[Guest]

Your tblWTAX table would make a very good spreadsheet; however, it just won't
work in a database.

Instead of Field 1, 2, 3, etc., across you probably need %, constnt, S, and
ME2 as the fields and the related data down like so. Then you could link by
the ME2 field and gather the data.

% constnt S ME2
..5 5 0.00 100 100
..10 70.00 1500 2300

Also Please do NOT use % as a field name. Although Access allows it, never
use any special character, except for the Underscore, as part of a field,
table, form, or report name. Just ABCs and 123s. No spaces either.

 

[Guest]

Hi Jerry,

Thanks man. Ok i will fix my database. Then plese help me with the codes. I
need it badly. I am confused.