Running Total column in query

[Guest]

I have a several columns in a single table, one of which ("FTE") will be
storing totals for a grouped item ("GRADE") and there will be another column
("TARGET") I want to add a 3rd column which will be a running total of the
"FTE" column to compare against the target and select a cut off point:

GRADE FTE R/TOT TARGET
01 40 40 150
02 80 120 150
03 50 170 150

I have searched this group and seen some SQL but have tried to replicate it
with bizarre results that was certainly not a running total, but I know
nothing about SQL so could have misunderstood.
It has to be in a query as this is only the first step in a much greater
series, the rest of which I could cope wih if I could get a running total
going. Is it possible?
If it is possible and it IS via SQL could you explain how very gently please?
Thanks for your help in advance.

 

[Guest]

For this solution to work, the "Grade" column must contain unique values.

for the R/Tot column use

DSUM("[FTE]","YourTableName","[GRADE] <= " & [Grade])

if Grade is of type text change this to

DSUM("[FTE]","YourTableName","[GRADE] <= '" & [Grade] & "'")

This effectively adds up the FTE values for the current record and all
earlier records. It has to do this for each record that is displayed, so if
you are working with a large table you will probably take a prohibitve
performance hit. In this case you may need to resort to hand coding.

 

[Guest]

Thanks so much, this works!
At first I thought it was SQL, but it can actually be pasted directly into
the standard design build area of the query.
I have looked at this and the MS Office Help "DSum" example and haven't
worked out how they are related (or exactly how this works), but that's a dig
at the help file not your answer!

For this solution to work, the "Grade" column must contain unique values.

for the R/Tot column use

DSUM("[FTE]","YourTableName","[GRADE] <= " & [Grade])

if Grade is of type text change this to

DSUM("[FTE]","YourTableName","[GRADE] <= '" & [Grade] & "'")

This effectively adds up the FTE values for the current record and all
earlier records. It has to do this for each record that is displayed, so if
you are working with a large table you will probably take a prohibitve
performance hit. In this case you may need to resort to hand coding.

I have a several columns in a single table, one of which ("FTE") will be
storing totals for a grouped item ("GRADE") and there will be another column
("TARGET") I want to add a 3rd column which will be a running total of the
"FTE" column to compare against the target and select a cut off point:

GRADE FTE R/TOT TARGET
01 40 40 150
02 80 120 150
03 50 170 150

I have searched this group and seen some SQL but have tried to replicate it
with bizarre results that was certainly not a running total, but I know
nothing about SQL so could have misunderstood.
It has to be in a query as this is only the first step in a much greater
series, the rest of which I could cope wih if I could get a running total
going. Is it possible?
If it is possible and it IS via SQL could you explain how very gently please?
Thanks for your help in advance.