Cumulative total error in query

[maxmaggot]

Hi all,
I really need your help with a query that calculates the cumulative total spent on expenses as each month passes. Here is the query I have written:

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];

The line that is for the cumulative total/running total is

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

I really appreciate any help and my employer is really turning the screw to get this feature to work. I just get error in the field for this query section.

I don't think I'm too far off but any help would be greatly appreciated as I can copy it into 6 more similar queries.

 

[maxmaggot]

Ok I changed the query and can get results now but they are off the scale wrong.

Here is the 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];

Line causing problem

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

Anyone see a solution? Thank you

 

[rzw0wr]

Please read the following.

http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

Dale

 

[maxmaggot]

Apologies for this. I'm desperate

 

[JHB]

Apologies for this. I'm desperate

Then post you database with some sample data + tell in which form, report or query you are using the shown query.

 

[maxmaggot]

I used a cross tab query in the end to get cumulative totals. Myself and rzw0wr where pming because I posted the db with some private info. I thanked him in those messages. The solution is a bit messy so if you want to have a look at it I can pm you the link. Thanks for responding and thanks again to rzw0wr who put a lot of effort into this.

 

[JHB]

If you have got the problem solved, then ok, else PM me the link and also write where there are problems.

 

[maxmaggot]

It's ok, customer is satisfied with what I have. Thanks