Running Sum in Union Query

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I have a union query extracting data from 3 different
tables - Bin Stock, Purchase Orders and Requirements. The
resulting data looks like this;-

ProductID
Due Date (for purchase orders & requirements)
BinLocationID (for stock)
TransactionType (1=Stock, 2=Purchase Order, 3=Requirement)
Qty

I need an extra field (running total) which will be the
Stock on Hand figure after adding/subtracting the Qty
field of each record. Unfortunately there is no unique id
which rules out using DSum. The output needs to be
displayed rather than printed. Does anyone know how I can
achieve this?

Thanks in advance for your help.
 
Hi,


RunningQty: DSum("Qty", "Tablename", "ProductID=" & ProductID & " AND [Due Date]<= " & Format(
[Due Date] , "\#mm-dd-yyyy\#") )


For a given record under evaluation at a moment, first, we need to evaluate the string used as
third component. Then resulting string will be a string where actual values for ProductID, say 33,
and actual due date, say the first of January 2001, have been incorporated in the string:
"ProductID=33 AND [Due Date]<= #01-01-2001#"


The DSum will then be executed based on that string used as criteria, which will sum values for UP
TO the given due date, making the running sum.


Hoping it may help,
Vanderghast, Access MVP
 
Hello,

Thank you for your reply. I don't think I was specific
enough when I first described the problem as multiple
transactions can happen on the same date but need to be
displayed individually as below;-

Product Reference Type Due Date Qty Free Stock
P1 BinLoc1 1 500 500
P1 BinLoc2 1 250 750
P1 POrder1 2 01/08/03 100 850
P1 Req1 3 01/08/03 -300 550
P1 Req2 3 02/08/03 -600 -50
P1 POrder2 2 05/08/03 350 300
P1 Req3 3 05/08/03 -150 150

In the Union query, I can create a unique record number
(using a function that adds 1 to a global variable), but
when I use this in the DSum, the running total field is
blank. If I write the output of the union query to a
table first, it does the running sum calculation ok. I
wanted to avoid (if possible) doing it this way because
this is a routine that will be run many times (by 3 or 4
people) during the day and I am concerned about slow
response times.

Many Thanks
-----Original Message-----
Hi,


RunningQty: DSum("Qty", "Tablename", "ProductID=" &
Click to expand...
ProductID & " AND [Due Date]<= " & Format(
[Due Date] , "\#mm-dd-yyyy\#") )


For a given record under evaluation at a moment, first,
Click to expand...
we need to evaluate the string used as
third component. Then resulting string will be a string
Click to expand...
where actual values for ProductID, say 33,
and actual due date, say the first of January 2001, have
Click to expand...
been incorporated in the string:
"ProductID=33 AND [Due Date]<= #01-01-2001#"


The DSum will then be executed based on that string used
Click to expand...
as criteria, which will sum values for UP
 
Back
Top