help with inventory design (1 Viewer)

so here's the requirement,

1. items in the inventory can be manipulated as. add-in, shipped-out(for testing) and shipped-back(same item as the shipped out), sold, expired

2. means of adding item to the inventory
--- a regular add-in form
--- shipped back item
--- imported from another company

3. means of deleting an item - none
--- or not? i need to track if its sold or expired, so i concluded that it should not be deleted in the database.

4. should be able to track when(date) the item is add/shipped/sold/expired
5. every item is unique.

bear with me and thanks in advance guys.

so yeah, sorry about that,
i already designed the tables and forms(the regular add in and the sold)
what i am getting confused about is, the part where, i should shipped out an item and get it back with the same serial number, normal inventory supposed to delete items(datas) when it is not currently in my warehouse, how should i get back the info about that certain item when i deleted it already.

someone suggested that, i should use two query, 1 for the items that got add-in and 1 for the shipped back,
but since i will not delete any item in my database coz i need it to track its status, it would take time to load all data everytime i open the database if its in a query.

This smells like homework--which isn't necessarily bad, we are still willing to help. What is bad is you've just dumped requirements on us and asked us nothing. You've not provided us with any work you have done up to this point, nor asked us about issues with which you are struggling.

So, my advice is to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), google for some inventory system Access databases you can use for guides, then give it a shot yourself. When you reach a specific issue you need advice on, post back here.

So - the $64,000 question - What have you got so far?

Post up the tables in a picture from the relationships window. Then we can advice.

If you haven't got anything I would suggest googling for some ERD templates for inventory to get some starting points.
http://logisticsglobal.blogspot.co.uk/2011/07/example-er-diagram-of-inventory.html etc.

Might be part of his thesis?

https://access-programmers.co.uk/forums/showthread.php?t=298110

plog said:

This smells like homework--which isn't necessarily bad, we are still willing to help. What is bad is you've just dumped requirements on us and asked us nothing. You've not provided us with any work you have done up to this point, nor asked us about issues with which you are struggling.

So, my advice is to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization), google for some inventory system Access databases you can use for guides, then give it a shot yourself. When you reach a specific issue you need advice on, post back here.

Click to expand...

There are many posts on many forums related to Inventory and google/bing can be your friend to find information.

You may get some ideas/concepts from this free video on Stock Control.

Your comment on an item possibly being "expired" may signify a more deep rooted issue -- FIFO/LIFO.

Here is a link to info that may be useful as you plan and design your database.

Good luck with your project.

jdraw said:

There are many posts on many forums related to Inventory and google/bing can be your friend to find information.

You may get some ideas/concepts from

Your comment on an item possibly being "expired" may signify a more deep rooted issue --



Good luck with your project.

Click to expand...

thanks, im going to read that FIFO/LIFO thing

Do you delete entries from your table or do you have a flag that indicated they are "Deleted" i.e. out of inventory?

Please set up your Relationships tool. Try to expand so we can see all the fields in your tables, take a screen shot of it, and post it here.

A comment on what you have described.

If every item is unique (perhaps because of a serial number) then the question about deletion makes a lot of sense. But I would limit deletions to specific events. For example, if you have a monthly, quarterly, or yearly "cleanup" cycle, keep deleted inventory for one full period after they are sold, shipped, expended, expired, etc. - for record-keeping purposes. This allows the items to go on a sales-of-this-month (or this quarter or this year...) report. This allows you to track sold items for a little while in case you get a late return or a return that crosses a cleanup boundary.

It would be a matter of policy as to whether you would allow a return after a full cleanup cycle has elapsed. Sometimes, you see, the problem isn't one that Access can solve. It doesn't make policy. YOU do. Do not look for Access to be a subject-matter expert. YOU have that distinction.

As to the rest of it, the other members have expressed their desires regarding what they feel they would need to see in order to help you better. I have nothing to add to that list.

@sciren13,

In the future, please let us know that you are asking for help with an assignment. This will help us tailor our response. It will also, hopefully, prevent tangents regarding items that SHOULD be included but are not in your initial requirements, such as how the system tracks inventory returned to the manufacturer (defect upon arrival OR warranty returns) that could otherwise detract from what you need.