Running Total

[ianc]

I would really appreciate some help. I've written the code below to produce
a running total in a query:

++++++++++++++++++++++++++++++++++++++
SELECT qry_stock_movement.stockistSN,
qry_stock_movement.grade_type_description,
qry_stock_movement.collection_date, qry_stock_movement.job_id,
qry_stock_movement.net_weight, (SELECT Sum(net_weight) FROM
qry_stock_movement As X WHERE X.collection_date <=
qry_stock_movement.collection_date AND X.stockistSN =
qry_stock_movement.stockistSN AND X.grade_type_description =
qry_stock_movement.grade_type_description ORDER BY
qry_stock_movement.stockistSN, qry_stock_movement.grade_type_description,
qry_stock_movement.collection_date) AS RunAmt
FROM qry_stock_movement
ORDER BY qry_stock_movement.stockistSN,
qry_stock_movement.grade_type_description, qry_stock_movement.collection_date;
+++++++++++++++++++++++++++++++++++++++

The nested SELECT does produce a running total but not at the level
required. There are a number of records with equal "collection_date"s which
gives me repeated totals ("RunAmt" values) for each record where the
collection date is the same, when what I want is individual totals for each
record within the same "collection_date" (within "stockistSN" and
"grade_type_description"). I can't see how to enhance this query further to
achieve this and have tried adding "AND X.job_id <=
qry_stock_movement.job_id" within the WHERE clause of the nested SELECT but
as the "job_id"s are not always sequential, this fails.

I hope this is enough detail, thanks in advance for any help.

Ian

 

[Lord Kelvan]

....

can you give us a bit of sample data of the before and after where the
before is the raw data and the after is what you want it to be

as a note you may have to do two queries

NOTE THIS IS A GUESS BUT IT WORKS ON MY DATA WHEN I HAVE TWO DATES THE
SAME

as a note this will only work if jobid is not duplicated for the
duplicated dates where stockistsn and grade_type_description are the
same

as this as an expression into your query designer as i cannot be
bothered translating this into sql.

runamt: IIf(
(select count(collection_date)
from qry_stock_movement as x
where x.collection_date = qry_stock_movement.collection_date
and X.stockistSN = qry_stock_movement.stockistSN
AND X.grade_type_description =
qry_stock_movement.grade_type_description)>1,
(SELECT Sum(net_weight)
FROM qry_stock_movement As X
WHERE x.job_id < qry_stock_movement.jobid
and X.collection_date = qry_stock_movement.collection_date
AND X.stockistSN = qry_stock_movement.stockistSN
AND X.grade_type_description =
qry_stock_movement.grade_type_description
ORDER BY qry_stock_movement.stockistSN,
qry_stock_movement.grade_type_description,
qry_stock_movement.collection_date),
(SELECT Sum(net_weight) FROM
qry_stock_movement As X WHERE X.collection_date <=
qry_stock_movement.collection_date AND X.stockistSN =
qry_stock_movement.stockistSN AND X.grade_type_description =
qry_stock_movement.grade_type_description ORDER BY
qry_stock_movement.stockistSN,
qry_stock_movement.grade_type_description,
qry_stock_movement.collection_date)

basically the if statment checks if there are duplicate dates then if
there are it does the running total thing based on jobid if not it
does it based on your standard statement which works from what i
understand. as a note i tested this on a count statement not a sum
statement as i use this method to make on the fly record numbers for
queries though it should work.

Hope this helps

Regards
Kelvan

 

[ianc]

Many thanks for your help I'll take a look at your suggestion