Running Sum in Query (DSum)

[Lewe22]

I have a query pulling information from multiple tables, one of which [tblDistLearnTrack] contains a list of students with an auto number [dist_auto_num] their assessment information and how long the assessment took in minutes [distduration].

My query is quite complex, using many tables and containing various parameters based on another form to show a list of assessments specific to a student.

I am trying to achieve a running total of [distduration] alongside the assessment length in my query and am having some difficulty. I realise i have to use DSum but can't quite get it to work.

I have pulled into my query all of the field names mentioned above and currently have the following entered as an expression in my query:

Running Total: DSum("[distduration]","tblDistLearnTrack","[dist_auto_num] <= [dist_auto_num]")

At the moment when running the query my 'Running Total' column contains no data. Where am i going wrong????

[Stewart Ross]

Running Total: DSum("[distduration]","tblDistLearnTrack","[dist_auto_num] <= [dist_auto_num]")

At the moment when running the query my 'Running Total' column contains no data. Where am i going wrong????

Well, the WHERE part of the DSUM can't discriminate the right rows, as you are comparing a field to itself within the expression.

[dist_auto_num]<=[dist_auto_num]

At best, this will always evaluate as True (because a field is always less than or equal to itself in value) in which case you should have had a total returned - wouldn't have been the right total, but a total nonetheless. At worst, if the DSUM could not evaluate the expression as valid, you would get no total returned. I wonder what has happened in your case? It could also be that DSUM cannot compute a total for the distduration field for some other reason I can't guess at.

Anyway, you will need to supply DSum with the value of the current autonumber for it to work. Assuming that the autonumber field is the same name, there is just a minor adjustment to make:

Running Total: DSum("[distduration]","tblDistLearnTrack","[dist_auto_num] = " & [dist_auto_num])

I have the nagging feeling that the DSum approach may not work well in this case, but without further details on what the underlying queries are I can only point out the flaw in its original form and leave it to you to test that any totals returned in due course are accurate.

-Stewart

[Lewe22]

Stewart,

I tried your formula but all it did was show the same value that is displayed in [tblDistLearnTrack].[distduration]. Any other ideas?

The way of my thinking is that every time a record is created in [tblDistLearnTrack] an auto number is assigned, increasing every time.
Therefore i need a way of totaling the [distduration] for all records with a smaller auto number.

Also, i am wondering if the DSum will need to include the same parameters as my query. Does it work independently from the query result set?

[Stewart Ross]

Hi lewe22. The result you found with DSUM is what I was referring to when saying I doubted the use of DSUM in this case. It seemed to me that you were unclear what kind of running SUM you wanted.

I suggest that you consider in more detail what you are trying to achieve; I am pretty sure it is not a running total of the assessment durations, at least not in the way you have tried to do so up to now.

If I read your first post correctly, you have a single row for each student, identified by your autonumber field. You have a nominal and actual assessment duration recorded for each of those rows. If you just sum based on the autonumber as the row selector you will indeed just return the current value of the duration, as you have found, as you are summing the one row only.

What is it you are trying to sum? Is there more than one row for each student, which there needs to be if you are to use SUM at all? Do you really need a running sum of the duration? If you have more than one row, what then discriminates one student from the next? (Whatever it is, it is this you would need to use in the DSUM, not the autonumber.)

I would be glad to assist you, but I think you need to be clear what it is you are trying to achieve and I am unable to glean this from your posts so far.

-Stewart

ps Ahh, you have added to your post - let me comment to see if I can assist.

[PianoMan64]

Stewart,

I tried your formula but all it did was show the same value that is displayed in [tblDistLearnTrack].[distduration]. Any other ideas?

The way of my thinking is that every time a record is created in [tblDistLearnTrack] an auto number is assigned, increasing every time.
Therefore i need a way of totaling the [distduration] for all records with a smaller auto number.

Also, i am wondering if the DSum will need to include the same parameters as my query. Does it work independently from the query result set?

Lewe22,

Your going to have to post what querys and table structure you have in order to be able to give you a specific answer. From what I've read so far, it looks like you don't have a start or a stop time post anywhere in your table structure, and you're going to have to have that in order to compute the difference in time between two fields.

Most of what you're trying to do can be done with a Group query so that you can group together what values you need and it will make them unique for each record returned.

Let's see what you have, and I can give you a more detailed answer.

Thanks,

Joe P.

[Stewart Ross]

Yep, I go with Joe P. You can't use the autonumber alone to do the sum - otherwise how do you know where one student's record changes to the next? You are missing the student reference from your table - which is the true key for the student, not the autonumber field.

-Stewart

[Lewe22]

tblDistLearnTrack contains a number of fields but for the purpose of what i'm trying to achieve it contains [dist_student_ref], [c_session], [c_code], [dist_auto_num], [distduration].

dist_student_ref, c_session and c_code are passed to the query from the previous form which restricts the query to 1 student for 1 course for 1 session (year)

[NeoPa]

A sideways view :
It is almost never appropriate to use running sums in a query.

Whenever you think one is required, it is almost certain that another approach makes more sense, one that you are simply not seeing.

Totals are possible, across groups.
Records in a query should rarely be cosidered as ordered data. Think of them as unique sets of data which all, individually, match a certain pattern.

Where running sums may sensibly be required, is in a report. Reports DO support this concept.