OK, but this type of query likely will run slowly. It is based on a
"ranking" concept that is summing the invoices that that are "less than or
equal to" the current one ("less" is a relative term, based on a specific
unique value for each record, in this case I assume invoice number -- I also
am assuming that Invoice Number is a numeric field):
SELECT [Invoices].[Customer Name], [Invoices].[Invoice Date],
[Invoices].[Invoice Number], [Invoices].[Description],
Sum([Invoices].[Invoice Total]) AS [SumOfInvoice Total],
[Invoices].[GL Number],
Format(DSum("[Invoice Total]","Invoices","[Customer Name] = 'All Clad'
And [Invoice Number]<=" & [Invoice Number]),"$0,000.00") AS RunTot
FROM Invoices
WHERE Invoices.[Customer Name]="All Clad"
GROUP BY [Invoices].[Customer Name], [Invoices].[Invoice Date],
[Invoices].[Invoice Number], [Invoices].[Description], [Invoices].[GL
Number]
ORDER BY [Invoices].[Customer Name], [Invoices].[Invoice Number];
--
Ken Snell
<MS ACCESS MVP>
patti said:
Thank you for your suggestion but a report is too limiting for the single
data base I'm using and the need to break down the companies and account
numbers individually for review and totals.
However,per your original reuqest, here is the SQL (I figured it out):
SELECT [Invoices].[Customer Name], [Invoices].[Invoice Date],
[Invoices].[Invoice Number], [Invoices].[Description],
Sum([Invoices].[Invoice Total]) AS [SumOfInvoice Total], [Invoices].[GL
Number], Format(DSum("[Invoice Total]","Invoices","[Customer Name] = 'All
Clad'" & " "),"$0,000.00") AS RunTot
FROM Invoices
GROUP BY [Invoices].[Customer Name], [Invoices].[Invoice Date],
[Invoices].[Invoice Number], [Invoices].[Description], [Invoices].[GL
Number]
HAVING (((Invoices.[Customer Name])="All Clad"));
If you could be of any further help, I'd greatly appreciate it. Thank
you!
Ken Snell said:
A report can do this for you as part of its normal setup. To do it in a
query is not straightforward.
You would group the report based on the company. Put a textbox in the
group
footer's section that has a control source similar to this:
=Sum([Invoice Total])
That will show you the total for each company individually.
If you also want a grand total, then put a textbox in the report's footer
section, with the same control source as noted above.
--
Ken Snell
<MS ACCESS MVP>
I'm trying to sum the invoices for a particular company. Each record
contains the company name, date, invoice #, detail of invoice, invoice
total
and account charged to. Would like to keep a running total of the
invoice
totals. And I'm really new to this. What is the SQL statement?
SORRY!
:
You never change the criterion expression in the DSum expression, so
of
course the DSum will return the same value for every record.
What are wanting to calculate the Sum of? Tell us more details. DSum
is
slower than using the Sum aggregate function in the query -- is it
possible
that you can use it? Post the SQL statement of the query.
--
Ken Snell
<MS ACCESS MVP>
The formula works RunTot: Format(DSum("[Invoice
Total]","Invoices","[Customer
Nmae] = 'All Clad'"), $0,000.00"), however, instead of a running
total
I'm
getting a sum total (the exact same number) on each line. Note: the
total
is
correct. I think it's reading it as a Sum instead of a DSum. Any
suggestions? Thanks!
