DSum Query over group (1 Viewer)

[Gord11]

Hello,
I am very new to access, i wanted to create a project and got stuck creating a running sum over a group in a query.
What i have is a table called "tblDemand", the table consists of ID (autonumber), ItemCode, Description, Quantity, Due.

I have then created a query from the table, and sort ascending by ItemCode and Due.

What i wanted to create is this;
ID PartCode Description Quantity Due Running Total
1 110603 Part1 100 12/10/2017 100
2 110603 Part1 10 14/10/2017 110
3 110587 Part2 250 11/10/2017 250
4 110587 Part2 100 15/10/2017 350

I tried using Running TotalSum("[Quantity]","tblDemand"," [ItemCode] ")

I have tried just about every method i can find on forums to get this to work, but just get errors or bad expressions.
I understand that i have not got enough information in the DSum, and i should be creating a more unique reference, but everything i try fails.

Any help would be apprieciated.

Thanks

 

[arnelgp]

add this as your Running Sum:

SELECT ID, PartCode, Description, Quantity,
Due, (SELECT Sum(Due) From tblDemand AS T1 WHERE T1.PartCode=tblDemand.PartCode AND T1.ID <= tblDemand.ID)
AS [Running Total] FROM tblDemand;

 

[Gord11]

Hello arnelgp,
Thank you for your reply.
I changed the "PartCode" to "ItemCode", my error in above expanation.

However i get strange results.

First line has a quantity of 1000 and a running total of 122918, second line -200, running total of 208970....
The ItemCode is sorted in order and so is the date.

I am not sure i understand T1, and can't work out the running totals.

Thanks

 

[arnelgp]

add this as your Running Sum:

SELECT ID, ItemCode, Description, Quantity,
Due, (SELECT Sum(Quantity) From tblDemand AS T1 WHERE T1.ItemCode=tblDemand.ItemCode AND T1.Due <= tblDemand.Due)
AS [Running Total] FROM tblDemand ORDER BY ItemCode, Due;

 

[Gord11]

Hello arnelgp,
Many thanks - it's close, but it seems to be missing the running sum when more than one line has the same date. I think i have read about this needing a unique identifier.

The actual running sum based on ItemCode is working.

I have attached the screenshot.

Thanks

 

[arnelgp]

the id will be our Unique Identifier, but as
i analyze your ID its inconsistent with
respect to Due Date (it should be ascending
together with date). must be the way you enter
data (in random date or in bulk entry).

if it is ok for you to call a function
to do the running sum?

copy and paste the code in a Standard
Module. then call it within the Query:

SELECT ID, ItemCode, Description, Quantity, Due, fnRunningDue(ID, ItemCode) AS [Running Total] FROM tblDemand;

Public Function fnRunningDue(id As Variant, Item As Variant) As Double
    Dim rs As DAO.Recordset
    Dim running As Double
    id = CLng(id)
    Item = CStr(Item)
    
    Set rs = CurrentDb.OpenRecordset("SELECT id, Quantity " & _
        "FROM tblDemand WHERE ItemCode = " & Chr(34) & Item & Chr(34) & " " & _
        "ORDER BY ItemCode, Due ASC;")
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        .FindFirst "ID = " & id
        If Not .NoMatch Then
            While Not .BOF
                running = running + !Quantity
                .MovePrevious
            Wend
        End If
        .Close
    End With
    Set rs = Nothing
    fnRunningDue = running
End Function

 

[Gord11]

Hello arnelgp,
Fantastic, this works perfectly.
You are correct, the data was added from an excel append to table.

I am not going to understand your function for a while, but it gives me something to start with.

Many thanks.

 

[arnelgp]

Glad that it worked. You can always ask Google for those things you dont understand. Goodluck.