Re: SQL Running Total

Mark, he wants a running total, i.e., for each distinct day a count of all rows where the day is less than or equal to the current day when the data is sorted by day.

1. First of all, Tom, you need some way to indicate to access what order days of the week should appear, since they won't naturally sort in the order you want them as presented below. So make another table called DaySort with fields Day ("mon", "tue", etc.) and SortOrder (1,2, etc.). Create and save a query called DetailTableSort that joins teh Detail Table with the DaySort Table.
2. Use that query as the base table for another query, thusly: SELECT [DaySort],[Day], DCount("*",[DetailTableSort]", "[DaySort] <= " & [DaySort]) FROM [DetailTableSort] GROUP BY [DaySort],[Day];

This will be very slow if you have a lot of data, but it should work.

Cheers,
Jon Myers

Mark D Powell wrote in message <8e9jct$fg$1_at_nnrp1.deja.com>...
>In article <8e9ehr$qvs$1_at_nnrp1.deja.com>,
> porchdog_at_my-deja.com wrote:
>> I'm trying to write a query which will display a
>> running total. For example, if a table has a
>> day-of-the-week column, I want to display each day
>> with a cumulative row total.
>>
>> Detail Table
>> ------------
>> Row Day
>> --- ---
>> 1 mon
>> 2 mon
>> 3 mon
>> 4 tue
>> 5 tue
>> 6 wed

<snip>

>Tom, I am not sure I understand your problem. As stated you should be
>able to do this (using data you provided):
>
Received on Thu Apr 27 2000 - 00:00:00 CEST