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

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: MS Access FAQ (Read Me First - NO SERIOUSLY)

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)

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.

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

This query:
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.

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

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

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.