Summing Values

atgordon

Board Regular
Joined
Nov 17, 2004
Messages
118
I have a table in Access that has invoice numbers, dollar amounts and pay dates. Since some of the invoices are paid in installments, an invoice number might be listed twice, each time with a different dollar amount and pay date. I would like to create something akin to a pivot table in excel where I can have a unique listing of invoice numbers with a total amount paid on that invoice. I know they have pivot tables in Access, but I am not sure if that would work, as after I have the unique invoices and total payment amounts in a table, I want to query that table for other uses.

Any help that can be provided would be greatly appreciated. I am not that familiar with Access, so the easiest way is best.

Thanks.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Build a Totals query.
In Design view in the query, add InvoiceNo, PaymentAmt and ClientName (this may come from another table, via a relationship -- just add the Clients table to the query as well).
Click the Sigma button on the toolbar to display the Totals row. All items will be Group By at this stage. Change PaymentAmt to Sum.

Denis
 

Forum statistics

Threads
1,136,368
Messages
5,675,358
Members
419,565
Latest member
Phil57

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