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
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.
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.
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
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