Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    6

    Unanswered: Trouble with Expression

    I am working on an inventory database needed for medication samples for an indigent clinic. I would like to be able to maintain a perpetual intentory. There are several transactions that are possible and I have a table which reflects this (samples, sample distribution, original balance, expired meds). Where I have run into trouble is that I need to distinguish between the quantity of the transaction and the actual quantity which affects our inventory. For example, expired meds may be a quantity of 7, but the actual quantity for our inventory is -7. I do not know how to write an expression that expresses each transaction.

    So far I've got, which is not yielding a quantity:
    Actual Quantity: IIf([Transaction Types].[Add/Remove]="Addition",[Inventory Transactions]!Quantity,-([Inventory Transactions]!Quantity))

    Any further help in the right direction would be much appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a transaction type table which identifies what the transaction is, a code and a correction factor (usually + or -1) that transactions affect on the stock level eg:-

    issues, disposals, returns (to supplier) have a negative affect on stock.... the stuff, like Elvis, has left the building

    deliveries from suppliers, returns form clinic (to the stores) have a positive affect on the stock.

    then when you run your stock query you apply the correction factor

    select sum(Quantity * CorrectionFactor) as StockLevel from mytable
    join TransactionTypes on TransactionTypes.TXCode = mytable.TXCode
    group by mytable.StockID
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2011
    Posts
    6
    I got it working now, thanks for your help

Posting Permissions

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