Running Total w/out storing & using Dsum??

[Topher]

hi there,

i have been trying to create a query that will give me a running total for purchase order quantities. i am using two tables. one which has the item on the purchase order and one table which has the recieve info for that item. each item can be received multiple times thus multiple receiving quantites.

so far the only way to get the total is to store it. and from experience and searching thru some other messages this is NOT the way to go. yet how do i create a query that will keep a running total. DSUM.

YET using Dsum my query gives me the total of ALL the items received in the PurchaseOrder regardless of the item. i would like to have the total PER item

so the output is as follows

Item # Qty Ordered QtyReceived
1 5 5
2 2 2

right now i get 7 for both items!

any help much appreciated, here is what my query looks like:

SELECT tblPurchaseOrders.ItemNum, tblPurchaseOrders.Qty,
DSum("[QtyReceived]","tblReceivedToPO","[PID] IN (Select ID FROM tblPurchaseOrders WHERE PONum = 'SP010604-02')") AS Expr1
FROM tblPurchaseOrders
WHERE (((tblPurchaseOrders.PONum)="SP010604-02"))
GROUP BY tblPurchaseOrders.ItemNum, tblPurchaseOrders.Qty;

the reason for this query is to display it in a listbox on a form.

- Topher

[This message has been edited by Topher (edited 06-04-2001).]

 

[Rich]

Do you need a running total or just the actual total at any given time? The latter being much easier to achieve.

 

[Topher]

just a total at any given time. so if the form is reloaded or an item added/received etc... it will be totaled and displayed to the user - without the need to store the #

have u any ideas??

Topher

 

[HeatherE]

I didn't see an answer to Topher's question; I'm having the same sort of problem with a report that I'm designing. Right now I have the total for the freight charge on the report as a DSum total. But, when I run the report it gives me the total freight charges to date rather than just the total for the specific date I'm running the report for.

Can anyone help me out with this? Is there a way to get just the freight charge for the requested period on the report?

[This message has been edited by HeatherE (edited 01-08-2002).]

 

[Rich]

Is the freight charge for each order listed on the report?

 

[HeatherE]

No, right now the Freight Charge is only listed as a total on the report. The report is a Summary report of all sales made within a specific period that the user designates. The report is titled “Sales by Product” and only lists the products and units sold for the period designated; it doesn’t list each individual order. The DB that I’m using was created from the Order Entry DB wizard and I’ve been modifying it as needed by adding reports and so on.

Does that clear things up at all?

 

[Topher]

Hey HeatherE,

The only way i could figure this one out was to record the total - which is annoying and not the most reliable. but its the only way i could get it to work the way i wanted.

no one ever posted a way to fix this nor could i find anything else on the net.

it sounds though you need an extra sort of filter with your code in order get the info between the dates you want.

one thing ive figured out is to create one query with the information you want and then create another query using the information from the 1st query. follow me?? again not the best way im sure but hey it works.

- T

 

[Pat Hartman]

You can add a footer to the subform that shows the order details. Add a control with a controlsource that uses the Sum() function.

=Sum(YourAmtField)

You may need to resize the subform on the mainform to get the footer to be visible.

 

[Rich]

Sorry Topher, for some strange reason I missed your reply in June. Do your tables have a sequential ID field? And are they ordered by that field?

 

[HeatherE]

So how would I create a query that would only total the freight charge for the time period selected by the user?

 

[Topher]

Pat - I can't use the footer b/c i need the information to show up in a listbox - i want it all together.

Rich - yes they do have a sequential id field - i think i tried making a query that didnt use that field but then i still get the wrong information somewhere. its bin awhile though ..

HeatherE - this should work

SELECT sum(Orders.Freight)
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrderDate) Between #8/8/1994# And #9/1/1994#));

it gets the total frieght cost based on the dates. if that doesnt then first create a query that says - i want these records from day 1 to day n. just get all of them even if you don't want them later.

SELECT Orders.Freight, Products.ProductName, Orders.OrderDate FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrderDate) Between #8/8/1994# And #9/1/1994#));

then create another query based on the 1st query that just totals the field you want. b/c the 1st query has information between day 1 and day n there is no way it can get any other dates.

SELECT Sum(Query1.Freight) AS SumOfFreight
FROM Query1;

actually now that i think bout this some more - if it is in a report have the report linked to the 1st query - if the report is a summary then you can setup it up in such a way that fields are counted or calculate by editing the recordsource for each txt box. ie counting how many sales, how many products, total frieght.

how do you have the connection setup so that when the user selects a date it goes through to the report?? do you pass a filter through to the report? there is an option you can use on in the form which uses the print/preview button. the command line that prints/preveiws you can add a filter to that to pass it through.

hope this helps.

- Topher

[This message has been edited by Topher (edited 01-09-2002).]

 

[Rich]

Topher this function will give you a running sum in a query
Function RunningSum(Source As String, KeyName As String, KeyValue, _
FieldToSum As String)
'***********************************************************

' FUNCTION: RunningSum()
' PURPOSE: Compute a running sum in a query.
' PARAMETERS:
' Source - The table containing the previous value to
' retrieve.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldToSum - The name of the field in the previous
' record containing the value to retrieve.
' RETURNS: A running sum of the field FieldToSum.

' EXAMPLE: =RunningSum("Table","ID",[ID],"Amount")
'***********************************************************
Dim RS As Recordset
Dim Result

On Error GoTo Err_RunningSum

' Get the form Recordset.
Set RS = CurrentDb().OpenRecordset((Source), dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, _
DB_SINGLE, DB_DOUBLE, DB_BYTE

RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
GoTo Bye_RunningSum
End Select

' Compute the running sum.
Do Until RS.BOF
Result = Result + RS(FieldToSum)

' Move to the previous record.
RS.MovePrevious
Loop

Bye_RunningSum:
RunningSum = Result
Exit Function

Err_RunningSum:
Resume Bye_RunningSum

End Function

 

[poduska]

Re: Running Total w/out storing & using Dsum??

I could never get DSum to work with my query with Dates but this works GREAT!
6 and 1/2 years later and the post is still helping!

You may never get this but Thank You RICH!

 

[Pat Hartman]

Re: Running Total w/out storing & using Dsum??

Just be aware that running sums in queries are extremely inefficient and you should not use them except in rare instances. Use a report to calculate the running sum. It is the most efficient method unless you want to write your own VBA.

 

[poduska]

Re: Running Total w/out storing & using Dsum??

I have found that out but I need to create the running sum as I am attempting to create "On Demand" Charts which plot the "Planned date" vs the "Actual Date" as a percentage of the total. So 20% is 9/12/08 and 30% is 9/30/08 etc up to 100% over the entire date range of the sample data (could be years). I have the percentage to work in some queries.

Obviously, this is easy in excel and I suppose I could code something to do this but it is a multi user and multi location environment and I just wanted an access chart to just pop up when a button is clicked, you know, keep it as simple as possible.

Haven't figured it out all yet, I am using a "Date" table to fill in the missing dates between data points (seems awkward but works) but the queries don't all work yet.

I'm only talking about 2-5k records and they can wait a minute or so for the results.

Thanks for your interest.

 

[Rich]

Re: Running Total w/out storing & using Dsum??

You may never get this but Thank You RICH!

You're welcome