Cumulative values

[Guest]

I have a table of dates and fertiliser applications and need to make a query to get a cumulative fertiliser application for each date. Can you tell me how, please?

 

[JulieD]

Hi Rohan

create a normal select query that lists all the dates & the amount applied,
in the query design view, click on the Totals icon (left of the word "All"
on the toolbar)

this adds another line to your query grid of "total"
under the date choose "Group by" and under the amounts of fertiliser applied
choose "total"

run the query and it should now do what you want

if you've never created a query before and need help on the first bit,
please post back

Cheers
JulieD

I have a table of dates and fertiliser applications and need to make a

query to get a cumulative fertiliser application for each date. Can you
tell me how, please?

 

[Guest]

Thanks, Julie but I'm after cumulative values, not just a total e

date Fertiliser weigh
1/1/04 3k
2/2/04 2k
5/2/04 1k

should return in the quer
1/1/04 3k
2/2/04 5k
5/2/04 6kg

 

[JulieD]

Hi Rohan

umm off the top of my head i wouldn't have a clue how to do it in access ...
hopefully someone else will be able to assist.

Cheers
JulieD

 

[Allen Browne]

Rohan, it's very easy to do this in a report. Just add an extra text box
bound to the [Fertiliser weight] field, and set its Running Sum property.

If you must do it in a query, you could create a calculated field to DSum()
the [Fertiliser weight] field, from the table, where the date is less than
the [date] field of your record. This will be very inefficient, and may not
yield the desired result if there are two identical dates. (A subquery would
be a little more efficient, but the results would be read-only.)

If you really have a field named "date", you are also likely to run into
ambiguity problems. Access may think you are referring to the built-in name
Date (used for the system date).