none
Access 2016 How do you filter to display accumulative total from the same source instead of individual records? RRS feed

  • Question

  • What I’m attempting to do is as I add data from the same source to accumulate instead of showing individual records. For example: Company Apple produces several Products as do company Banana and Cantaloupe.

    Company:           Product:               Qty:

    Apple                    Widget                 5

    Apple                    Widget                 2             

    Apple                    Whatnots             3

    Banana                 What have ya       4

    Cantalope              Thingamajig        8

    As you can see I received 2 more widgets from Apple but I would like the Qty to change to the new Value of 7 instead of showing:  Apple       Widget          2

    And the same with the other company’s as I increase the Qty on hand.

    I am not using any dates (no need to to) Just want to Qty to change as I add or delete.

    Now I could just go ahead and change the value in the Qty box but I prefer to use the “Add New” from the form which uses a drop down control for both the company name and the Product description. Any help would be appreciated. Thanks for you time and help.

    Dave

    Friday, June 28, 2019 8:31 PM

Answers

  • You might like to take a look at Balances.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates a number of queries for computing balances.  The demo deals with financial data , but the methodology is exactly the same for other quantitative data like yours.  The most efficient method is a JOIN, but that returns a non-updatable recordset, so if you want to use an updatable query as a form's RecordSource you'd need to call the DSum function.  The option for this  in your case would be 'Single table with single column for amounts - grouped – updatable'.

    You will need a TransactionDate or similar in the table to do this.  You'll note that the queries in the demo use the table's primary key as the tie breaker in the event of two or more transactions taking place on the same date.  If this is not necessary, and as you are not summing currency values, the query can be simplified to:

    SELECT TransactionID, CustomerID, TransactionDate, TransactionAmount,
    DSum("TransactionAmount","Transactions","CustomerID = " & [CustomerID] &
    " AND TransactionDate <= #" & Format([TransactionDate],"yyyy-mm-dd") & "#") AS Balance
    FROM Transactions
    ORDER BY CustomerID, TransactionDate;

    Ken Sheridan, Stafford, England



    Saturday, June 29, 2019 11:14 AM

All replies

  • You can calculate totals by using a totals query. See https://support.office.com/en-us/article/sum-data-by-using-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a#bm4

    Assuming your table is called tblProducts the SQL for your query would be

    SELECT tblProducts.Company, tblProducts.Product, Sum(tblProducts.Qty) as TotalQty
    FROM tblProducts
    GROUP BY tblProducts.Company, tblProducts.Product

    Saturday, June 29, 2019 12:26 AM
  • You might like to take a look at Balances.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates a number of queries for computing balances.  The demo deals with financial data , but the methodology is exactly the same for other quantitative data like yours.  The most efficient method is a JOIN, but that returns a non-updatable recordset, so if you want to use an updatable query as a form's RecordSource you'd need to call the DSum function.  The option for this  in your case would be 'Single table with single column for amounts - grouped – updatable'.

    You will need a TransactionDate or similar in the table to do this.  You'll note that the queries in the demo use the table's primary key as the tie breaker in the event of two or more transactions taking place on the same date.  If this is not necessary, and as you are not summing currency values, the query can be simplified to:

    SELECT TransactionID, CustomerID, TransactionDate, TransactionAmount,
    DSum("TransactionAmount","Transactions","CustomerID = " & [CustomerID] &
    " AND TransactionDate <= #" & Format([TransactionDate],"yyyy-mm-dd") & "#") AS Balance
    FROM Transactions
    ORDER BY CustomerID, TransactionDate;

    Ken Sheridan, Stafford, England



    Saturday, June 29, 2019 11:14 AM
  • To do what you are attempting, using a simple aggregating query like that suggested by Alphonse would give you the right results of course, but the form would not be updatable.  Thinking about this a little further I've made a small amendment to my Balances demo.

    If you select the 'Separate credit & debit tables  - final balances  - read only' option in the demo, I've added an 'Add' button to the detail section of the form which opens  a little dialogue form.  This enables you to add a new transaction.  In my case the balances are derived from separate Credits and Debits tables, but with a single transactions table you would not need the Credit/Debit option group in the dialogue form.  Nor would you need the Select Case construct in the Confirm button's code; you'd simply execute an SQL statement to insert a row into the table.

    If you wanted to cater for items removed from stock as well as additions, you could of course keep the option group and Select Case construct, and if a removal from stock is selected multiply the quantity by -1 when inserting the row into the table.  The aggregated value would then be reduced by the quantity removed.

    Ken Sheridan, Stafford, England

    Sunday, June 30, 2019 4:27 PM
  • Thanks guys, I appreciate your time in answering my question. The Grouping with the sum in the query worked for me. Going with the "Widget" in the example above allows me to enter the data and when the product is selected it will group that product giving me the updated quantity. My database is a little more complex then what the example is so it took a little while for me to piece it together to see what worked best. Sorry I didn't get back to you guys sooner. Thanks again guys. :-)
    Sunday, July 7, 2019 8:38 PM