Accounting operations

[history]

Hello Friends

I have two tables
1st ... employee table contains (ID,Name,Salary,........ etc)
2nd .. Account data contains ( A B C D E F G H I J K L M N O P Q R S T )

My Question is :
1- How to make a table (Name : Acctbl) to bring (ID,Name,Salary) from employee table and all fields from Account data table to be stored in it (Acctbl) .
2- I've a lot of equations ,,, I don't know how to do
I'm doing these equations in an Excel sheet ,,, but I need these equations to be applied in Access to be stored in (Acctbl) table.
So I need to make a form to enter specific data and to view calculations.
Fields : A B C D E F G H I J K L M N O P Q R S T
equations :
A=D-(B+C)
F=D/15*E
G=F*0.0125
H=F*0.01
I=G+H
J=F-I
K=J*0.03
L=100/30*E
M=IF(J<L,1*0,J-L)
N=IF(M>400,32,M*0.08)
O=IF(M>800,40,IF(M>400,(M-400)*0.1,0))
P=IF(M>800,(M-800)*0.15,0)
Q=N+O+P+H
R=IF(Q<=0,J-K,J-K-Q)
S=R*0.005
T=CEILING(S,0.5)

If you can help me to do ,, I'll be so appreciated or guide me to some where to learn about this topic.

Thanks a lot

waiting to hear from you

 

[kevlray]

Creating the table is easy, In my experience, I normally do not put calculated values into a database. I do the calculations on the fly (in a query or on a form). If you need these values in a database. Then I would create VBA code to pull in the data from Excel(?) and the employee table, do the calculations then write and SQL script (in VBA) to update the Acctbl table.

Lots of luck

 

[history]

Please waiting you

Please Friends

I'm confused
I need the results to be in the Account data table but with (ID,Name,Salary) from employee table ,,,,, I mean that this table will show the full data .. (ID,Name,Salary,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,Q,R,S,T)

Thanks friends for care

 

[kevlray]

So the only way I know to do it is to write VBA code that will query your data (Excel spreadsheet?, some other data source(s)), do the the calculations and write the data to the table.

Lots of luck

 

[DCrake]

Very quickly

Fields : A B C D E F G H I J K L M N O P Q R S T
equations :
A=D-(B+C)
F=D/15*E
G=F*0.0125
H=F*0.01
I=G+H
J=F-I
K=J*0.03
L=100/30*E
M=IF(J<L,1*0,J-L)
N=IF(M>400,32,M*0.08)
O=IF(M>800,40,IF(M>400,(M-400)*0.1,0))
P=IF(M>800,(M-800)*0.15,0)
Q=N+O+P+H
R=IF(Q<=0,J-K,J-K-Q)
S=R*0.005
T=CEILING(S,0.5)

Fields in bold are unknown quantities so would need to held in the table as fields. The remainder of the calculations can be replicated in queries dynamically.

The Ceiling function is not available directly in Access you would have to use a workaround to achieve this.

 

[gemma-the-husky]

what exactly are you trying to achieve.

Calculate gross pay?
Based on hours?
Based on piecework?
Calculate a net payroll?


what are the basic data inputs you have that drive this?

hours worked?
pay rates?
overtime rates?

etc etc


you first need to consider this sort of information, and analyse this into an appropriate data structure, before starting on the detail

 

[history]

what exactly are you trying to achieve.

Calculate gross pay?
Based on hours?
Based on piecework?
Calculate a net payroll?


what are the basic data inputs you have that drive this?

hours worked?
pay rates?
overtime rates?

etc etc


you first need to consider this sort of information, and analyse this into an appropriate data structure, before starting on the detail

Yeah ,,
I need the calculations based on days worked to be result as net payroll

Thank you

 

[gemma-the-husky]

i meant

i think you need to provide more info, than to just ask how to do calculations.

ie you probably need more than 2 tables, and it would be useful to know and understand exactyl what data you have - not the calculations you want to do