Databse Products Expiration Date

[PATRICK]

Ok, here's where I need some help. I'm need to implement this new inventory module into the system where it goes track in/out of products. till here is fine just in/ out but then some products has expiration date. my question is how can I track inventory on this products that has expiration date: lets say, I had inbound of SOAP 10 units, expiration date 3/1/2013. then I sold 6, then I have another inbound of 10 expiration that 4/1/2013. basically I need to know how many I received and sold from each expiration date, but I don't want to insert this product 2,3 times.. I need to keep only one product in the list.so lets say I have form where I put the amount that went out but then I just can link to the product because I'll select soap but how define about the expiration date.

[June7]

If product has an expiration date, are you pulling from shelves and disposing of product? Enter a transaction to account for the disposal. Otherwise, why is this needed?

I can understand that a net profit/loss calculation on first in/first out inventory basis needs to consider the product acquisition date and I suppose could use the expiration date in lieu of the purchase date but seems the outcome is the same. However, profit/loss calculation is not the same as determining stock on hand. Isn't it a given that the stock on hand is from the latest purchases? Might find this of interest http://www.allenbrowne.com/AppInventory.html

Also, Google: Access database inventory first in first out
http://www.dbforums.com/microsoft-ac...first-out.html

[PATRICK]

see, actually I have this module working fine. Update stock quantity, In/out detail all that. but what im trying to understand and make because I'm sure supermakets has this feature, lets say:

Product: Clenaer Unit: Gallons ID: 001
so, I purchase this item, I go and select the item to enter the detail information
ID: 001 Qty.: 10 Exp. Date: 2/03/2012 Operation: In
then I make some sales of this item:
ID: 001 Qty.: 6 Dt: Sale: 1/04/2012 Operation: Out
fine, my stock now will become (4). but then I have another purchase:
ID: 001 Qty.: 10 Exp. Date: 3/03/2012 Operation: In
now my stock will become (14). great!, then I'll start sale again:
ID: 001 Qty.: 5 Dt: Sale: 2/05/2012 Operation: Out

but I don't know if I sold from the one's already expired or not and even to know how many gallons I have expired. the amounts are all together, but from my second sale on 2/05/2012 I have some expired from 2/03/2012.

[June7]

I don't know that grocers do that detail of tracking (I have doubts). I suspect they have to physically examine stock on shelves, pull the expired and either destroy or discount price. These actions would probably involve transaction entries. You could visit one and ask.

[PATRICK]

well I guess I'll have to do that, visit one and ask because I can't imagine some BIG supermarket going physically see what is expired or not. They must know what is expired. Anyway thanks for reply, I appreciated.

[June7]

Somebody is checking expiration dates. I often buy milk on date of expiration that has been discount priced. On reflection I doubt they enter transaction at the time the item is retagged but the discounted sale price is recorded at the checkout. Whether or not management is tracking inventory by expiration date is the question. Just how would this benefit operations?

[PATRICK]

well with this information I could run a report and have exactly amount of items that are expired. lets say I have 10.000 items and many purchases on each item with different quantities and expiration dates, so without it I have to go physically to check and see what is expired and what is not, plus to order new items I need this result first so I can decide how many more I should need. Now imagine to have a list of expired item amount on hands and you could go right to the point and while doing this I could run new order on those that are expired. but like you said I'm curious now and I'll check it in person how works. I have a friend that owns a small supermarket maybe he has something working there.

[June7]

Okay, so the calculation tells you the expiration date for soandso purchased items has passed. But what does that really mean? What will you do with that info? It doesn't indicate whether those items are actually still on the shelf or sold out. You want to base stock replenishment on product expiration date? And if those items are still on the shelf do you pull and discard to be replaced with new product? What if product completely sold out long before the expiration date?

[PATRICK]

you are linking soandso purchase only to "one purchase". see this item can have 2, 3, 4 .. many purchases and all of it will be linked to one ID number of the product. but lets say purchase 1 was 10 item with expiration date, then it didn't sold out, balance is 6 because I sold 4. then I made purchase number 2 with 20 items of the same product with different expiration date. now I have balance of 26. what's going to happen is basically system will sum purchase number 2 with what has left of purchase number 1 but then later on I need to know if there are items expired on whatever was left on purchase number 1 because when I sale the only point of connection is UPC code. What I'm saying is without this information I have to go physically and check all the 26 items to see if there are any expired, do you understand? I can't choose when the system register from with purchase I need to make the sale but it has to be way to link it. hope you understand, but if not its ok, i'll find out. thanks for your interest in the subject.

[June7]

I can see now that the calculation would be useful as an alert: "WARNING expired product could be on shelf, go pull it so we don't poison customers with sour milk!" As noted in earlier referenced link and the reason I wanted to make sure you really, really need, this is not easy to do. First have to calculate the balance on hand then retrieve latest purchases that sum greater than or equal to the balance minus the purchases that have not exceeded their expiration date.

ASSUME CURRENT DATE = 1/8/2012
ExpDate	Purchases		SaleDate	Sales		OnHand
1/1/2012	10		1/2/2012	3
1/5/2012	8		1/7/2012	9
1/10/2012	5
	23			12		11

In this example can easily see that 6 of the items on hand must be from Purchases with 1/5/2012 ExpDate and have expired. A filter to exclude records where ExpDate is less than the current date is easy. Restricting recordset to just the records that sum >= the OnHand balance minus nonexpired is difficult because this criteria depends on values in other records. This requires subquery (or a series of saved query objects, each building on another) or domain aggregate function.

Here is a reference for subqueries http://allenbrowne.com/subquery-01.html, unfortunately none fit your situation.