Question Expire Items Solution

Moore71

DEVELOPER
Local time
Today, 08:44
Joined
Jul 14, 2012
Messages
158
Hi,
I have an Inventory solution.
I am ready to deploy it to a client, but the client ask me if the solution contains Items expiration date?
My puzzle now is that, which table will carry the expiration date and how do i do away with it when that Item has either been sold before the expiration date and new Items are being ordered into the database?
Hope my question is not confusing as such?

Regards,
Moore71
 
Not really enough info to guide you. I would assume that the date would be in your products table and then use an DateDiff statement for the Sold date.

With more info, maybe we can assist further.
 
umm - too little information and I'm not clairvoyant.

What tables do you have at the moment? it could be the item table, it might go into the pricing table or a stock transactions table table or perhaps you don't even need it - you would have a shelf life field which when added to the delivery date gives you the expiration date, or perhaps it is something picked up when the item is scanned.
 
As others have said, we need more info.
What kind of product are we taking about?
Show us your current tables and relationships (relationship window).
Please show us the list of requirements/specifications you have for this database.
Seems quite late in the process to be getting such information from client.
 
Mostly an inventory db will be based on movements of items - Supplier to Storage, Storage to Shelf, Shelf to Buyer ...
In your case you will have to move also the original expiration date. You will have to move a product from a specific date, and not just A product.
Just like in cars. You don't move 5 Fiat 500. You must know the specific car moved.
You can either move the expiration date, or (What I think is better) the original (Supplier to Storage) RecordID.
 
Thank you for your replies.
I was away for sometime to a remote place outside of network.
The tables are:
1- Items(ItemID,ItemName,CatID,AvailableQTY,CostPrice,UnitPrice)
2- Purchases(PurchaseID,ItemID,PurchaseQTY,ExtendPrice,PurchaseDate)
3- Order(OrderID,ItemID,OrderQTY,OrderDate,ExtendPrice,OrderDate) and so on.
My puzzle is which of the table is ideal to put ItemExpiryDate? If i put it in Items table which would have been better, but that will be duplicating the same item because of different purchase dates for same item.
Or please can you guide me here, so i will know when to flush away items that expired at their time.

Thank you for your continuous support.
Moore71
 
Just curious--- Do you have Customers? Suppliers? It seems that we are only seeing part of the picture. If every Item has an ExpiryDate, then it seems to be an attribute of Item.

PurchaseDates relate to Purchase. Are you saying that you need a way to ensure that PurchasedItems are purchased before their ExpiryDate?

I think it would be helpful to readers if you could describe the "big picture" so that we have some context for your questions. If you could tell us in plain English -no Access/database terms - what the business is about,and what you are trying to achieve, I'm sure it would be helpful to readers. As you can see from posts to date, there is not enough info to respond with focus.

There is a free data model here that may help put your info into context.
 
Last edited:
Please say more about what the expiry date is and how it is determined/calculated.

expiry date could be a 'best before date', a time limited offer, a date from which the item will no longer be available or any number of other things.

Each would have a different solution
 
Hi,
the big picture is that it is complete package that includes even the customers and suppliers.
What i am trying to avoid is duplication of Products/Items in the same table.If I enter Items expiration date for Perishable goods,let's say 1/1/2015 and before those Product/Items where totally purchased, New Items of the same make were purchased with a new expiry date. How do I manage these 2 or more expiry dates of the same Items/Products?
What happens if someone is ordering some Items/Products,say 10,but 5are from old expiry date and the rest 5 are from the new purchased Items with new expiry dates.
Here is my puzzle...

Regards,
Moore71
 
Research FIFO Inventory control.

When posting please give the big picture to give readers an idea of the scope of your issue/opportunity. It is difficult to offer specifics when readers are guessing at the meaning of the post. We only know what you tell us, so paint a broad picture to give context, then add detail as necessary.
 
As I wrote before:
You must treat similar items with different expiration dates as different items.
 

Users who are viewing this thread

Back
Top Bottom