Cumulative total error in query

[maxmaggot]

I've been trying to get a cumulative total by month for expenses for the last few days and I just can't get it to work. Here is the code for the query

 

SELECT DISTINCTROW Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000") AS Expr1, Expenses.[Expense Type], Sum(Expenses.Amount) AS [Sum Of Amount], (DSum("Amount","Expenses","[Expenses].[Expense Type] <=" & [Expenses].[Expense Type])) AS [Running Total]
FROM Expenses
GROUP BY Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000"), Expenses.[Expense Type]
HAVING (((Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000")) Not Like "?-?"))
ORDER BY Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000"), Expenses.[Expense Type];

It's this line that won't give in

(DSum("Amount","Expenses","[Expenses].[Expense Type] <=" & [Expenses].[Expense Type])) AS [Running Total]

Anyone able to help?

I can PM someone the db if necessary

[rzw0wr]

What data type is Expense Type?
If it is text then your quotes are wrong.
You are showing it as a number.

Dale

[maxmaggot]

The database is going up on a Sharepoint server so I have tried to limit the number of relationships. The data type for Expense type is a lookup field. i would love to move it out to its own table and link it but I get web compatibility issues when I do that. Hope that helps.

I changed quotes to the following

(DSum("Amount","Expenses",[Expenses].[Expense Type] <= [Expenses].[Expense Type])) AS [Running Total]

I don't get an error but it gives me just insane values.

Could I PM you the db if this doesn't work. My boss is nailing me to the wall to get this working?

[maxmaggot]

Ok I fixed quotes and get values now but they are totally of the scale. Any ideas anyone
New Code

SELECT DISTINCTROW Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000") AS Expr1, Expenses.[Expense Type], Sum(Expenses.Amount) AS [Sum Of Amount], Sum((DSum("Amount","Expenses",[Expense Type]<=[Expenses].[Expense Type]))) AS [Running Total]
FROM Expenses
GROUP BY Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000"), Expenses.[Expense Type]
HAVING (((Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000")) Not Like "?-?"))
ORDER BY Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000"), Expenses.[Expense Type];

cumulative line

Sum((DSum("Amount","Expenses",[Expense Type]<=[Expenses].[Expense Type]))) AS [Running Total]

Any help?

[rzw0wr]

leave the sum() off the Dsum()
then try it witout the where clause.

Dale

[maxmaggot]

ok changed code to

SELECT DISTINCTROW Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000") AS Expr1, Expenses.[Expense Type], Sum(Expenses.Amount) AS [Sum Of Amount], (DSum("Amount","Expenses",[Expense Type]<=[Expenses].[Expense Type])) AS RunningTotal
FROM Expenses
GROUP BY Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000"), Expenses.[Expense Type]
HAVING (((Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000")) Not Like "?-?"))
ORDER BY Format(DatePart("m",[InvoiceDate]),"00") & " - " & Format(DatePart("yyyy",[InvoiceDate]),"0000"), Expenses.[Expense Type];

Problem line

(DSum("Amount","Expenses",[Expense Type]<=[Expenses].[Expense Type])) AS RunningTotal

Getting results but still insane values.

Sorry for the hassle.

[rzw0wr]

I thought you would. The problem appears to be in you where clause.

Not knowing what data the expense type holds as far a values. I can't offer any advice.

I know expense type is text. you are trying to filter less than or equals to text. This will not work.
Even if the the text is a number symbol "6" does not = 6.


Dale

[rzw0wr]

Max, This what you looking for?

or are you looking for a grand total of ALL the expenses?






Dale

[maxmaggot]

That is what I am seeing

In the Query - Expenses Query Main it sums the total for each expense by month. What the customer wants is another field "RunningTotal" that would, for instance, show the total for each expense in month 2 + month 1 and so forth e.g. month 3 would show the Running total for month 1+2+3. Hope that helps. I'm generating a report from this query and filtering it by month.

[rzw0wr]

I am thinking what you want to do could better be done in a report or maybe a form.

Then you could use VBA a lots more commands.


Dale

[maxmaggot]

Yeah, I'm thinking that myself at this stage. Thanks for the help. I'll start coding now. At least I can close down this path of trying to do ti.

Again thanks for all your help.
Karl

[rzw0wr]

You have a PM.
Dale

[maxmaggot]

Thanks for your help today. Didn't get a solution but I tried a cross-tab query for the first time and while the result isn't pretty in report view it is able to accumulate totals with alot of extra work

Hope that helps someone else if they are looking for help on this.

Again not a pretty solution and most likely not ideal but something that worked for me (hopefully)

Thank you

[June7]

Report would probably be best output vehicle for this grouping and summarizing. Textboxes on report have a RunningSum property.