If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DSum Total
My formula works perfectly and this is the SQL: 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","[GL Number] = '68300-750'And [Invoice Number]=" & [Invoice Number]),"$000.00") AS RunTot FROM Invoices GROUP BY [Invoices].[Customer Name], [Invoices].[Invoice Date], [Invoices].[Invoice Number], [Invoices].[Description], [Invoices].[GL Number] HAVING (((Invoices.[GL Number])="68300-750")) ORDER BY [Invoices].[Invoice Date]; The fields are Customer Name, Invoice Date, Invoice Number, Description, Sum of Invoice, GL Number and Run Tot. This all works, however, there are few customers and they repeat. When I single out a specific GL Number to run a total, with repeat customers, sorting in ascending order by date, the Run Total totals the first date and each matching customer associated with that date, then finds the next earliest date and totals each matching customer in that group, therefore I end up with the correct run total somewhere in the middle of the report instead of totaled straight down by date only. Any suggestions? Thank you! |
#2
|
|||
|
|||
You don't say how your report is organized, but it sounds like you have none
or incorrect groups. If you want to report sub-totals by customer, create a group (with header & footer) based on customerID. -Ed "patti" wrote in message ... My formula works perfectly and this is the SQL: 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","[GL Number] = '68300-750'And [Invoice Number]=" & [Invoice Number]),"$000.00") AS RunTot FROM Invoices GROUP BY [Invoices].[Customer Name], [Invoices].[Invoice Date], [Invoices].[Invoice Number], [Invoices].[Description], [Invoices].[GL Number] HAVING (((Invoices.[GL Number])="68300-750")) ORDER BY [Invoices].[Invoice Date]; The fields are Customer Name, Invoice Date, Invoice Number, Description, Sum of Invoice, GL Number and Run Tot. This all works, however, there are few customers and they repeat. When I single out a specific GL Number to run a total, with repeat customers, sorting in ascending order by date, the Run Total totals the first date and each matching customer associated with that date, then finds the next earliest date and totals each matching customer in that group, therefore I end up with the correct run total somewhere in the middle of the report instead of totaled straight down by date only. Any suggestions? Thank you! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
using a form parameters to open a report | jkendrick75 | Setting Up & Running Reports | 5 | January 14th, 2005 05:05 PM |
Dsum or other method to calculate running total spend. | PETER | Using Forms | 8 | December 13th, 2004 04:53 PM |
using a DSum() function in a form footer | Paul James | Using Forms | 12 | July 31st, 2004 05:06 AM |