add calculation to fields

everwhat

Board Regular
Joined
Jul 23, 2002
Messages
189
I am new to access and I do not know how to sum a row in table and multiple data from one table to another. The common fields between the two tables are period, year and dept.
for example:
Table 1
Period Year Dept Admin Expense Other add field to add across
08 2006 07333 2.00% 23.00% 56.00% 81.00%
08 2006 07222 23.00% 50.00% 27.00% 100.00%
09 2006 07333 60.00% 30.00% 10.00% 100.00%
09 2006 07222 30.00% 60.00% 10.00% 100.00%
10 2006 07333 10.00% 40.00% 40.00% 90.00%


Table 2
Period Year Dept category Amt add field where amt *admin add field where amt *expense add field where amt *other add field to add across
08 2006 07333 ab $450.00 ($450*2%) ($450*23%) ($450*30%)
08 2006 07222 dc $473.00 (10000.*23%)
09 2006 07333 ab $496.00
09 2006 07222 dc $519.00
10 2006 07333 cr $542.00

thank you,
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
It looks like you want to multiply the percentages from Table1 with the dollar amount in Table2 in a query or report. Is that correct? If so, what is the 10000.*23% in the second row of Table2?
Is this for a report, or query, or what?
Thanks,
 

everwhat

Board Regular
Joined
Jul 23, 2002
Messages
189
I'm sorry, I made a keying error by typing in 1000.*23%. It should be $473.00 * 23%. The period, year and dept will match from both tables. What I want to do is if the period, year and dept match, multiple the amount from table 2 by the percentages from table 1.

Also in table 1, create a field where you can sum the row. For example , 2% + 23% +56% = 81%.
 

everwhat

Board Regular
Joined
Jul 23, 2002
Messages
189

ADVERTISEMENT

It is a query. Sorry about that.
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
Use the Query By Example screen that comes up when you create a new query. Add the two tables to the query, do the necessary joins, then put these entries in the "Field" row.
Admin: Table1.Admin * Table2.Amt
Expense: Table1.Expense * Table2.Amt
Other: Table1.Other * Table2.Amt
Total: (Table1.Admin + Table1.Expense + Table1.Other) * Table2.Amt

Each of these previous four lines goes into it's own column on the query grid sheet.
 

everwhat

Board Regular
Joined
Jul 23, 2002
Messages
189

ADVERTISEMENT

Thank you!
 

everwhat

Board Regular
Joined
Jul 23, 2002
Messages
189
One more item, is there a way to add row in table.

For example the format of table where column "Total" adds Mar, Apr, & May".

Dept Mar Apr May Total
89 $50.00 $50.00 $25.00 =sum the of Mar + Apr +May ($125)
34 $22.00 $21.00 $15.00 =sum the of Mar + Apr +May ($58)
 

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
To sum the three columns Mar, Apr, and May would be just like the sum that was done in the last line of the sample I showed you before. The only difference is that you would not need to multiple by the Amt from Table2.

After you are done with this addition, are you going to store the value in the table? Or is this just a query as before?
 

everwhat

Board Regular
Joined
Jul 23, 2002
Messages
189
This would be a new table and the Total field/column would be stored in the table only.
 

Forum statistics

Threads
1,136,352
Messages
5,675,280
Members
419,559
Latest member
BraytonM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top