running balance! 1

[PAULCALLAGHAN]

I'm using Access 2000 and trying to create a simple query that will give me a running balance in each record.

The criteria to drive the records I want is easy. But I can't seem to come up with a simple summation type running balance of one of the fields in my query, inv_qty.

I'm having trouble doing this. Can someone please lend a hand?

 

[PHV]

Without your table schema nor input samples nor expected result it's hard to say ...

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

 

[PAULCALLAGHAN]

Sorry dude.

the fields are inv_date, inv_num, cust_id, part_num and inv_qty.

My criteria is give me all such records for a given part_num, over a particular date range.

My query is called Query1.

For each record I would like a running total of inv_qty.

 

[PHV]

A starting point:
SELECT A.part_num, A.inv_date, A.inv_num, A.cust_id, A.inv_qty, Sum(B.inv_qty) AS Total
FROM yourTable A INNER JOIN yourTable B
ON A.part_num = B.part_num AND A.inv_date >= B.inv_date
GROUP BY A.part_num, A.inv_date, A.inv_num, A.cust_id, A.inv_qty

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

 

[PAULCALLAGHAN]

Sorry PHV, that doesn't work.

Error message: "You tried to execute a query that does not include the specified expression 'inv_qty' as part of an aggregate function."

The error message does not specify from which table it is referring to the inv_qty field.

Any other suggestions?

 

[PHV]

Can you please post your actual SQL code as mine include inv_qty in the group by clause ... ?

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

 

[PAULCALLAGHAN]

Thank you for your diligence, and concern, PHV.

However, maybe we can try a different route. You responded to 'Cinfysue' on thread '701-940343', and this looks very similar to what I am trying to do, except I don't have the user prompt for a starting value.

You suggested a slight modification to her DSUM formula. Can you please explain that modification to me?

 

[PHV]

Again, what have you so far ?

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

 

[PAULCALLAGHAN]

This is what I have typed in so far,

SELECT A.part_num, A.inv_date, A.inv_num, A.cust_id, A.inv_qty, Sum(B.inv_qty) AS Total
FROM yourTable A INNER JOIN yourTable B
ON A.part_num = B.part_num AND A.inv_date >= B.inv_date
GROUP BY A.part_num, A.inv_date, A.inv_num, A.cust_id, A.inv_qty

 

[PHV]

And where is the problem ?
You reall have a table/query named yourTable ?

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

 

[PAULCALLAGHAN]

PVH,

Do you mean this,

SELECT [Invoice Detail].PRTNUM_32, [Invoice Detail].INVDTE_32, [Invoice Detail].INVCE_32, [Invoice Detail].CUSTID_32, [Invoice Detail].INVQTY_32, Sum([Invoice Detail1].INVQTY_32) AS Total
FROM [Invoice Detail] INNER JOIN [Invoice Detail1] ON [Invoice Detail].PRTNUM_32 = [Invoice Detail1].PRTNUM_32
GROUP BY [Invoice Detail].PRTNUM_32, [Invoice Detail].INVDTE_32, [Invoice Detail].INVCE_32, [Invoice Detail].CUSTID_32;

 

[PHV]

SELECT A.PRTNUM_32, A.INVDTE_32, A.INVCE_32, A.CUSTID_32, A.INVQTY_32, Sum(B.INVQTY_32) AS Total
FROM [Invoice Detail] A INNER JOIN [Invoice Detail1] B
ON A.PRTNUM_32 = B.PRTNUM_32 AND A.INVDTE_32 >= B.INVDTE_32
GROUP BY A.PRTNUM_32, A.INVDTE_32, A.INVCE_32, A.CUSTID_32, A.INVQTY_32;


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

 

[PAULCALLAGHAN]

I waited for approx. 2 minutes, then

"ODBC - call failed. S1T00Timeout expired."

any other suggestions?

 

[PHV]

SELECT PRTNUM_32, INVDTE_32, INVCE_32, CUSTID_32, INVQTY_32, DSum("INVQTY_32", "[Invoice Detail]", "INVDTE_32<=#" & Format([INVDTE_32],"yyyy-mm-dd") & "#") AS Total
FROM [Invoice Detail]

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

 

[PAULCALLAGHAN]

I'm getting something, but I don't know what!

I switch to Design view and add some simple criteria for PRTNUM such as Like "1818*" and I give a date range for INVDTE.

But the new result shows some extremely large numbers under the Total column, and they don't appear to be 'true' running totals/sums. The rest data looks fine though. what should I do now?

 

[PAULCALLAGHAN]

Thank you PVH,However I'v been giving this some thought and want to do things different.

You've been busting your butt over this, and I think I've come up with another approach that I feel will be easier. I'm going to end this thread and start a new one, thats similar but with a different focus on my problem.

Thanks again PVH.