Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004

    Unanswered: Query to display quantities and running totals?

    I am trying to find out how to make an access query which will add, subtract and display a running total of a quantity of items.

    I have two related tables, tblItems (itemID (pk), ItemName, startingQuantity) and tblQuantity (itemID, quantityID (pk), quantityUp, quantityDown, quantityChangeDate).

    I need to be able to track when the quantities of an item went up or down and calculate the running total based on the starting quantity.

    An item may start with a quantity of say 10, then on January 1st it may drop to 3, then on Febuary 1st another 5 may be added. So the query must show this.

    Im really at a loss where to start!

  2. #2
    Join Date
    May 2004
    New York State
    The structure of your tblQuantity is imperfect. You should have one field, quantityChange, instead of Up and Down fields. The Change field can record both additions and subtractions, as well as the change date and any other appropriate comments (such as addition source or subtraction reason), as long as each record is only recording one transaction. This is called normalization of data.

    In other words, even if on, say June 28th you have both a plus and minus transaction, you can record the plus in one record and the minus in another.

    Continuing with your question. You can then make a query (qryQtySub, perhaps) as follows:
    SELECT tblItems.itemID, Sum(quantityChange) AS SumOfChanges FROM tblItems INNER JOIN tblQuantity ON tblItems.itemID = tblQuantity.itemID GROUP BY tblItems.itemID;
    You would then create another query that includes the two tables "as is", joined on itemID, and also include the sub-query I described above, with its itemID field also joined to tblItems.itemID.

    If I were you, though, I wouldn't display the results in a query (it's too unreadable), but rather in a report, where you could add the starting quantity plus the addition of all the changes, as well as displaying the dates and any other comments. The report would have this second table as its Record Source.

    Good luck,


  3. #3
    Join Date
    Aug 2004
    Thankyou for your reply, I will post back here if I get stuck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts