Running total in query using DSum

[a_ud]

Help needed. I'm stuck with a query that calculates the running total of a simple table like this one:

Table: T1

Field 1
John
Charles
Tom
John
............
​

The query (say Q1) should show this:

Field 1 -CountofField1 -Cumulative
John - 2 - 50%
Charles - 1 - 75%
Tom - 1 - 100%

that is: a) the count of individual cases and b) the running total for that record. No problem with a), not sure about b). So far I've got something like this (designed through the Access grid):

SELECT Field1, CountofField1 as X, DSum("X", "Q1", criteria)

This shows the correct numbers for the count (CountOfField1). It could also show the running total, but lacks the criteria to include only up to the current record, which I'm not able to find.

Questions:

• Any ideas for the criteria for DSum here?
• If not, any other way of finding the running total with this design (pls don't propose reports or the like) ?

Thanks in advance, a.

 

[pr2-eugin]

Have you tried using the 'Totals' from the Query Design tools? I think you do not even need the DSum method. You can alter the Query Q1, using the Totals and the resullt I believe will be more than enough.
STEPS:
* Open the Query Q1 in design view. Add Field1.
* Click on the Totals button. It will bring all the funvtions available in the total Row.
* Select Count for the second column.
* Select Sum for the third column.

Hope this helps.

 

[a_ud]

Thanks for the try, Eugin, but I think that doens't work.

As it is now, I'm using 'Totals' in the query and I have:

[Field1] - [CountofField1] -

If I add, for instance, [SumofField1] in the 3rd colum, I'd get a 'Data type mismatch' error message.

Either I'm not getting you or you're not getting the difficulty of it

 

[pr2-eugin]

Yes, Sorry I did not get that. I thought you had another column which had something like mark/percent from which you wanted sum. What do you mean by Cumulative?

NOTE: You cannot run a Sum on a Text Column. That is the reason for Data type mismatch error.

 

[a_ud]

In my first post cumulative simply meant the running total of each entry in the table. The 50% comes from:

-> Count of 2 records for John (correctly calculated by second field, CountofField1) divided by total number of entries (4 records in total).

And so do the 75% [(2+1) /4] and the 100% [(2+1+1)/4]. in other words, cumulative is the same as the running total in a spreadsheet.

 

[pr2-eugin]

Hi, I am not sure how you would achieve this with only one field in place specially if it being a Text field.. Well I am sorry I really could not help you. I did lookout in the interent, and I found this. Hope this helps.

http://bit.ly/LRiRFv