Help converting MySQL SELECT to JET-SQL

Alexandre · May 11th, 2004, 11:35 AM

I have the following table:

Name Date value
test1 10/10/04 4
test2 10/10/04 2
test3 10/10/04 1
test4 10/10/04 3
test5 10/10/04 2
test6 10/10/04 1

I want to made a SELECT to return the following table

Name Date value
test1 10/10/04 4
test2 10/10/04 6
test3 10/10/04 7
test4 10/10/04 10
test5 10/10/04 12
test6 10/10/04 13

The MySQL syntax of the SELECT is:
SELECT name,data,@aux:=value+@aux

How can I do it in JET-SQL?

Steven · May 11th, 2004, 10:55 PM

Code:

SELECT * FROM YourTable ORDER BY [Value]

I am a loud man with a very large hat. This means I am in charge

Bob Hansen · May 17th, 2004, 03:18 PM

Hi Steven, I think Alexendre is looking for a running total, not a sort by value. I suspect that this can only be done in a report and using a control with RunningSum function.

The numbers in his second display, third column are not values in ascending order, they are a running total caluclation of the values in his first example of the table contents. The second display is his query results of the table.

Using an alias in the query, like: Total:Value+Total ends up with an error because of a circular reference.

Steven · May 17th, 2004, 05:45 PM

Ah - right you are Bob.
Although, I guess you could also write a whole heap of queries and UNION them, but I agree, a running sum would be better

I am a loud man with a very large hat. This means I am in charge

Rood67 · May 18th, 2004, 11:05 AM

Download the qrysmp00 file from Micro$oft at this link. It has an example of how to do a running sum in one query.

http://support.microsoft.com/default...b;EN-US;207626

Hope this helps.

Rood67
Scott H.

Bob Hansen · May 19th, 2004, 01:37 AM

Thanks for the reference to the sample, but I have had no luck working with it. Here are my observations. Note, I removed formatting from my tests.
==========================
This is from the sample referenced above:
SELECT Orders.EmployeeID AS EmpAlias, Sum(Orders.Freight) AS SumOfFreight, Format(DSum("Freight","Orders","[EmployeeID]<=" & [EmpAlias] & ""),"$0,000.00") AS RunTot
FROM Orders
GROUP BY Orders.EmployeeID;
=====================================

This is my test. It gives "#Error" in RunningTotal column:

SELECT tblTemp.Name, Sum(tblTemp.Value) AS SumOfValue, DSum("Value","tblTemp","[Name]<=" & [Name] & "") AS RunningTotal
FROM tblTemp
GROUP BY tblTemp.Name;
===================================

A second test gives Overall Total, but not running total. Overall total is same value on each line.

SELECT tblTemp.Name, Sum(tblTemp.Value) AS SumOfValue, DSum("Value","tblTemp",Name<=Name) AS RunningTotal
FROM tblTemp
GROUP BY tblTemp.Name;

Steven · May 19th, 2004, 06:02 PM

Looks like you're missing a set of single quotes
try this:

Code:

SELECT tblTemp.Name, Sum(tblTemp.Value) AS SumOfValue, 
DSum("Value","tblTemp","[Name]<='" & [Name] & "'") AS RunningTotal
FROM tblTemp
GROUP BY tblTemp.Name;

I am a loud man with a very large hat. This means I am in charge

Steven · May 19th, 2004, 06:03 PM

That's assuming that tblTemp.Name is a text field of course

I am a loud man with a very large hat. This means I am in charge

Bob Hansen · May 19th, 2004, 08:16 PM

Good catch Steven......now it will only take me a year to break down and understand those additional quote marks. Why are they needed here?

They definitely are not in the sample code that was provided by Microsoft, regerenced by Rood67. And that code works for me. Is there some type of patch/change between versions that may have caused the need for additional quotes? I was doing test with Access 2000, and the samples are titled Access 2000 queries.

Snib · May 19th, 2004, 08:21 PM

Looks like you're referring to a value, not a column.

HTH,

Snib

<><