run sum

  • Thread starter Thread starter new access user
  • Start date Start date
N

new access user

how can i apply this to my query?
instead of category id and units in stock, use Sales-Q and
PRDCD-Q. How do i change this?

Option Compare Database
Option Explicit

Function fncRunSum(lngCatID As Long, lngUnits As Long) As
Long
'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long

If lngID <> lngCatID Then
'If the current ID does not match the last ID,
then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a
running sum for the ID.
lngAmt = lngAmt + lngUnits
End If

'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function
 
Hi,


You need a field that supply the "ordering", assuming its name is
DateTime, then


SELECT a.CatID, SUM(b.Unit)

FROM myTable As a INNER JOIN myTable As b
ON a.CatID=b.CatID

WHERE a.DateTime >= b.DateTime

ORDER BY a.CatID



should to. You can move the where clause in the join, if you wish (faster of
execution, but loosing the graphical representation in the grid):

SELECT a.CatID, SUM(b.Unit)

FROM myTable As a INNER JOIN myTable As b
ON a.CatID=b.CatID AND a.DateTime >= b.DateTime

ORDER BY a.CatID


Hoping it may help,
Vanderghast, Access MVP
 
I want to use the same code except change CatID to SALES-Q
and UNITS to PRDCD-Q
which parts of the code do i have to change?
 
Hi,


Well... Find all occurrence of CatID and replace it by SALES-Q, do the same
with UNITS and PRDCD-Q.


No ? Probably not, but I can't really decipher what is your problem, here.
Can you rephrase it? Or is it that the - sign in the (variable? fields? )
name make the name illegal, so use _ instead of - if possible.


Sorry, I can only guess about your problem.
Vanderghast, Access MVP
 
Back
Top