Subtotals??

[crystalfun]

I have a table with 190K rows that shows all invoices paid in 2001. Each row contains a vendor number, invoice number, etc.

I want to create subtotals at each change in vendor number to determine the total number of invoices sent by each vendor. I can do this easily in Excel using the subtotal menu but Excel can't fit 190K rows.

Can someone tell me a way to do this in Access?

 

[tlbroadbent]

If you only want to see the count sub-totals, the following should work.

Select
VendorNo,
Count(*) As InvCnt
From YourTable
Group By VendorNo

If you want to list all details, sub-totals and the grand-total in one query, you can use a UNION query like the following. I added another column, InvoiceAmt to expand the example.

Select
VendorNo, InvoiceNo,
InvoiceAmt,
" " As InvCnt
From YourTable

UNION ALL

Select
VendorNo, "Total:",
Sum(InvoiceAmt), Count(*)
From YourTable
Group By VendorNo

UNION ALL

Select
"Grand Total:", "",
Sum(InvoiceAmt), Count(*)
From YourTable Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.