Running balance SUB 1

[ktb916]

I'm trying to create (without much luck) a SUB that can calculate a running balance grouped on a unique part number. For instance, part number 1234 has three records in the table - for each record i want to be able to calculate a running balance so:
record 1 Qty 50 Balance 50
record 2 Qty 50 Balance 100

and so on.

There are several different part numbers in the table and i want to group on each one.

Any ideas??

Thanks,

KTB916

 

[PHV]

Provided you have an unambiguous way to sort the records you may achieve your goal with a single query.
So, what is the schema of your table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ktb916]

Let's say the table has three fields:

ID = double - this is a unique number that represents a measured diameter.

QTY = Long - this is the quantity to calculate the running balance

SeqNum = Long - sort field

I'm running Access 2000

 

[PHV]

Something like this (SQL code) ?
SELECT A.ID, A.QTY, Sum(B.QTY) AS Balance
FROM yourTable AS A INNER JOIN yourTable AS B ON A.ID=B.ID AND A.SeqNum>=B.SeqNum
GROUP BY A.ID, A.QTY


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ktb916]

This code will give the total balance but not a running balance. Somehow it's got to step through the table and add Qty's line for line. Thanks for the help - anymore suggestions are always welcome.

KTB916

 

[PHV]

OOps, sorry for the typo:
SELECT A.ID, A.SeqNum, A.QTY, Sum(B.QTY) AS Balance
FROM yourTable AS A INNER JOIN yourTable AS B ON A.ID=B.ID AND A.SeqNum>=B.SeqNum
GROUP BY A.ID, A.SeqNum, A.QTY
ORDER BY 1, 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ktb916]

Sweet - this works. What does ORDER BY 1,2 do?

 

[PHV]

Sort by 1st column then 2nd

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886