#### everwhat

##### Board Regular
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
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
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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,

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%.

It is a query. Sorry about that.

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.
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.

Thank you!

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)

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?

This would be a new table and the Total field/column would be stored in the table only.

Replies
2
Views
278
Replies
4
Views
480
Replies
4
Views
437
Replies
8
Views
541
Replies
0
Views
281

1,220,009
Messages
6,151,440
Members
451,028
Latest member
greekness1

### 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.

### Which adblocker are you using?

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

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