Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2011
    Posts
    17

    Unanswered: Stock Aging Reprot FIFO Mehtod

    Dear All,

    I am new to access and this forum. i am developing a access database for sales and inventory management where i need to have a inventory aging report based on FIFO mehtod i do have table purchase and sales

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Welcome to the forum!

    I'm afraid that you won't receive many answers if you don't elaborate a little bit more and be more specific in your request.

    See: http://www.dbforums.com/microsoft-ac...ml#post4534485
    Have a nice day!

  3. #3
    Join Date
    Dec 2011
    Posts
    17

    Stock Aging

    Thanks for you inputs

    i have a databse for sales management which has table for purcahse and sales

    i have an inward table you can say purchase with following detail

    inwDate
    Item
    Qty
    Amount

    Also I have outward table you can say sales detail table. which capture following details

    invdate
    item
    qty
    amount


    now my problem is on a particular date i want to see item wise aging following FIFO (first in first out)

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you are able to uniquely identify each item (I'm not sure it's the case with the table structures you describe), you can create an INNER JOIN query that will link each item in both tables using this unique identifier. In that query, you can create a computed column that will yield the difference (in days, month, minutes..., or any available time unit) between the purchase date and the sale date for each item. You can then add an ORDER BY clause to the query so that it will present the results in an ordered manner.
    Have a nice day!

  5. #5
    Join Date
    Dec 2011
    Posts
    17

    Stock Aging

    Thanks once again for your quick reply

    my problem is I am veru new to access and realy i do not have idea abouts joints but yes I do have unique identifier for each record following is the table structur and example of data for inward register.

    UniqueID Date Item Qty
    1 01-01-11 item1 45
    2 03-04-11 item2 90
    3 06-04-11 item1 105
    4 15-08-11 item1 43
    5 19-01-11 item2 65
    6 1-12-11 item1 54


    Following is the example for out ward register

    UniqueID Date Item Qty
    1 01-01-11 item1 30
    2 03-04-11 item2 45
    3 06-04-11 item1 25
    4 15-08-11 item1 35
    5 19-01-11 item2 20
    6 06-12-11 item1 54


    you can see from the table one that 247 units of item1 was purchased during the period and also 144 units were sold in the same period so i still have 103 units in stockof item one now what i exactly want the query which can give me the following results for remaining 103 items same is required for item2 also

    UniqueID Date Item Qty Aging
    6 01-12-11 item1 54 16
    4 15-08-11 item1 43 124
    3 06-04-11 item1 6 255
    uniqueid can be treated as batch no

    Regards

    Vishal

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This query:
    Code:
    SELECT   Inward.UniqueID, 
             Inward.Item, 
             Inward.Date, 
             Inward.Qty, 
             Outward.Date, 
             Outward.Qty
    FROM     Inward INNER JOIN 
             Outward ON Inward.UniqueID = Outward.UniqueID
    ORDER BY Outward.Item, Outward.Date DESC;
    will yield the data set shown in the attachment, however I don't understand how you compute the values for 'Qty' and 'Aging' in your example.
    Attached Thumbnails Attached Thumbnails Qry_InwardOutward.jpg  
    Have a nice day!

  7. #7
    Join Date
    Dec 2011
    Posts
    17

    Stock aging

    Thank you very much for ur reply.

    I also experimented and got some solution and my query will go like this

    SELECT Inward.id AS id1, Inward.dATEI AS dd, Inward.item AS ita, Inward.qty, DSum("qty","inward","[item] =" & [ita] & "And [id]<=" & [id1]) AS rsqty, IIf(IsNull(DSum("qty","outward","[item] =" & [ita])),"0",DSum("qty","outward","[item] =" & [ita])) AS sales, [rsqty]-[sales] AS yn, IIf([yn]<0,"No","Yes") AS agei
    FROM Inward LEFT JOIN Outward ON Inward.item = Outward.item
    GROUP BY Inward.id, Inward.dATEI, Inward.item, Inward.qty
    ORDER BY Inward.dATEI;

    now agin feild will return value as yes or no and I will use datediff for the feild having yes as output

    may be my procedure is bit lenthy but as i said i am new to access and this is the only way i got

    please give some inputs

  8. #8
    Join Date
    Dec 2011
    Posts
    17

    hello

    i need one more help please let me know how can i mark this tag as resolved

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This cannot work and your query has errors in several columns: rsqty, sales,yn, agei (see attachment). Domain function create their own data set, you cannot reference the running query in them.

    Moreover, the data struction does not correspond to the one you described in your first post and I still dont understand what you try to compute.
    Attached Thumbnails Attached Thumbnails Qry_DSum.jpg  
    Have a nice day!

Tags for this Thread

Posting Permissions

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