Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009
    Posts
    10

    Unanswered: Calcualtion Query in MS Access

    Hi

    I'm using Ms Access 2003 to create a DB for my school project and I need to accomplish the following:-

    * Calculate balances for stock (inclusive of sales and replenishment to stock)

    My table (StockDetails):

    StockId (PK)
    OriginalStock
    AmountSold
    AmtReceived


    I designed a query that says:
    SELECT StockDetails.StockId, StockDetails.OriginalStock, StockDetails.AmountSold, StockDetails.AmtReceived, [OriginalStock]-[AmountSold]+[AmountRcd] AS Balance
    FROM StockDetails;

    However, from this query I'm only able to have it function (calculate) correctly for the first entry, every other entry goes back to :-

    *Calculate from the original (OriginalStock) amount e.g. if the OriginalStock is 15 and 1 item was sold then it would be 14 as the Balance BUT 15 still remains as the original stock. So the next time I try to conduct a sale of this item it will once again assume the 15 as the OriginalStock.

    Thus I would like to have the Balance to be the same as the OriginalStock amount each time the level is altered either by sale or replenishment.

    I've tried designing many queries but none have been successful to accomplish the desirable.
    Any assistance rendered to achieve the result desired would be greatly appreciated.

    Thanks a mil!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Where and how do you modify the amounts in [OriginalStock], [AmountSold] and [AmountRcd]?

    Have a nice day!

  3. #3
    Join Date
    Aug 2009
    Posts
    10
    Hi Sinndho,
    Sorry about the late reply. Thanks for your response.
    I have readjusted the table to now reflect 3 separate tables so I'm now able to accomplish this by using the Sumof.... function.
    Many Thanks for your reply!

Posting Permissions

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