Cumulative Sales by Product

QueryProductSummary

Date      ProductID      ProductName      Price      Qty      SalesValue      Cumulative
01/01/2017      201        Tea Biscuits        £2.50        20        £50.00        £50.00
02/01/2017      201        Tea Biscuits        £2.50        15        £37.50        £87.50
03/01/2017      201        Tea Biscuits        £0.95       120 £114.00        £201.50
04/01/2017      201        Tea Biscuits        £0.85        16        £13.60        £215.10
01/01/2017      202        Milk Packets        £0.15        200      £30.00        £30.00
02/01/2017      202        Milk Packets        £0.16        150      £24.00        £54.00
03/01/2017      202        Milk Packets        £0.14        160      £22.40        £76.40
04/01/2017      202        Milk Packets        £0.12        185      £22.20        £98.60
01/01/2017      203        Rice Packets        £1.10        20      £22.00        £22.00
02/01/2017      203        Rice Packets        £1.05        12      £12.60        £34.60
03/01/2017      203        Rice Packets        £1.12        60      £67.20        £101.80

Above is an expected query from the following tables in Ms Access:
(1)      TblProducts
(2)      TBLPrice
(3)      TblCustomer
Now the issue here is to get the cumulative sales value by products like above , that is where I’m failing to archive , any idea here??

Regards

Chris

Nitin Sontakke

You may wish to have a look at this:

https://support.microsoft.com/en-in/help/290136/how-to-create-a-running-totals-query-in-microsoft-access

Pawan Kumar

Pls try DSUM

Cumulative SalesValue: DSum("[SalesValue]","qry","[ProductID]= '" & [ProductID])

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Thank you Pawan , the trouble here is the speed ,I guess it is due to the domain function DSum I'm afraid as the business grows we may no longer retrieve the data required, is there no way to do away with the domain functions? The same goes with Nitin.

Regards

Chris

Pawan Kumar

I think that depends on the data. Even if we write then may be also be slow down the line if data grows. So I think it depends...

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

I'm told a sub query can work in place of Domain functions , but I do not know how this can be constructed , any idea?????????

Regards

Chris

Pawan Kumar

Ok, Pls try this-

SELECT
     a.[Date]
	,a.ProductID      
	,a.ProductName      
	,a.Price      
	,a.Qty      
	,a.SalesValue      
    (
        SELECT Sum(a1.SalesValue)
        FROM yourTableName AS a1
        WHERE
                a1.ProductID = a.ProductID
            AND a1.[Date] <= a.[Date]
    ) AS [Cumulative]
FROM yourTableName AS a;

Open in new window

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

ASKER

Is a & a1 an alias????????

Pawan Kumar

Yes you are correct. They are alias.

PatHartman

1. in order for a running sum to work correctly in a query, you MUST have a unique identifier and the order of the results must reflect that. If you expect the results to be ordered by date but the data is not entered by date then you will need to make a concatenated sort field to make this work.
2. Are you certain you actually need this in a query? Doing the running sum is essentially free if you do it in a report because a report is a sequential operation. Just use the Running Sum property of a control
3. If you need this in a recordset and the recordset can be large, it might be better to create a code loop that reads the sorted recordset and writes out a temp table with the calculated running sum. As the recordset gets large, this will be faster than the query method and much faster than the domain function method. Keep in mind that domain functions run a query so if you have 10,000 rows in your recordset, you are running 10,000 separate queries to to the DSum()'s. That why the domain function method is so slow.
4. Access doesn't optimize subqueries well so when I have to do this in a query, I join the source table to itself using a non-equi join.

Select ... From tblA as a1 Left Join tblA as a2 ON a1.UniqueID >= a2.UniqueID

Pawan Kumar

hey Author - A feedback is appreciated.

ASKER CERTIFIED SOLUTION

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial