Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2008
    Posts
    3

    Unanswered: FIFO ( Fisrt In First Out )

    Dear all,

    I have problem to make data base using FIFO ( Fisrt In First Out ) metode. One product have many prices. Example:

    Table Order:

    name Qty Price /pcs
    1 Pen 100 150
    2 Pen 50 165


    Table Sales:
    name Qty Prices
    1 Pen 125 ( 100 * 150 ) + ( 25 * 165 )


    How to make it in query or crosstab?
    Thank you for your attention..

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ?
    FIFO is usually used in the context of stock control, or a stack. there should be no concept of prices at sales level.... you have a stock item which you may have bough at different times at different prices, you may have different prices depending on who the customer is (usually that is handled by means of a discount on 'the' price.

    but to have different prices at the same time based on what you paid for the stock in the first place is a new one on me

    I've seen FIFO used to come up with a stock value for accounting purposes, although thee days its more usual to use the last invoice as the cost price for valuation purposes.

    This doesn't sound like a real world problem. trying to track what items have been used to calculate sales price on a FIFO basis is going to be tricky......

    I don't see how a crosstab query is going to help you....

    you need to know what items are have been supplied at what prie and quantity
    you need to know what items are have been sold at what cost price and quantity to work out what the curent sales stock is beig drawn from.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2008
    Posts
    3
    Thanks for your attention Mr.healdem

  4. #4
    Join Date
    Feb 2006
    Posts
    11
    [tbl-Stock][part][qty][cost][retail]

    [TBL_delivered_Stock][part][qty][cost][date]

    use an update query to add the qty and cost of each item delivered
    Add qty to stock tble
    [tbl_Stock]![Qty]=[tbl_Stock]![Qty]+[forms]![delivered_Stock]![qty]
    Add cost to stock tbl
    [tble_Stock]![cost]=[tbl_Stock]![Cost]+([forms]![delivered_Stock]![qty]*[forms]![delivered_Stock]![qty])

    you are better running this as two seperate querys rather than trying to use code which will get painfully slow as your data grows.

    Selling the stock and deducting the cost of the first one in will need to ad another field to the delivered table so you can identify which stock has been sold.

    query by first record for the stock you are selling and reduce the cost on the stock tbl by the cost of the item you are selling.

    in accounting terms this model looks great but in reality you may need to resort to average costing because you may sell three items (cost 4 each) when the last 3 delivered were 4 but the next 3 were 4.5.

    Most companies use the average costing route. the only time you should find descrepancies is when you return stock to a supplier that might have been averaged down.

    Good luck

  5. #5
    Join Date
    Dec 2008
    Posts
    3
    Thanks acorn,

    I will try your intruction...I was make union to combine Sales Query and purchase Ouery.Query Stock to update the all stock and then I make crosstab to combine Union (sales and delivery table ) to Query Stock.

    Union ( sales and purchaes Query )

    No Date Code Invoice_Numbers Transaction Weighty @Price
    1 12/2/2008 Pen 123 Sales 1500 Check
    2 12/4/2008 Pen 345 purchaes 500 100

    Query Stock
    No Date Code Invoice_Numbers Transaction Weighty @Price
    1 12/1/2008 Pen Stock 1000 100
    2 12/1/2008 Pen Stock 500 120

    Crosstab ( Union and Query Stock )
    No Date Code InvNubers Stock @price Purchaes Sales Sum Remain
    1 12/1/2008 Pen 1000 100 100000 500
    2 12/1/2008 Pen 500 120 60000 0
    3 12/2/2008 Pen 123 1500 160000
    (1000*100)+(500*120)

    4 12/2/2008 Pen 345 100 500 5000

    Check = must check the first query stock, if enough use the price ( Weight * Price ) If the first stock query not enough use the second stock to reduce sales remain (1000 use the fisrt stock and 500 use second stock ) and then use the price suit the category.

Posting Permissions

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