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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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,
 
Upvote 0
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%.
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top