Simple inventory tracking database.

[Chunny]

Hi, I'm new here, and a very beginning Access user. I'm working on a project to create a simple inventory tracking database. I have created tables as follows.

Product
- Code
- Name

Movement
- Number
- Movement ("in" or "out")

MovementDetail
- Movement.Number
- Product.Code
- Quantity

Is this a good design for inventory database?

 

[macca the hacke]

What about date of movement (in MovementDetail). May also want to capture details of who initiated movement in the same table.
I don't see the point of your table called Movement. This could all be captured in MovementDetail. You will also need to capture PartNo in Movement Detail, or you won't know what part you are moving.
You may want to capture total number of each part that you hold, so that you can see how many are out, and how many remain at any one time.
Don't forget naming conventions too (ie all tables should start Tbl)

 

[Chunny]

Thanks for suggestions.

Yes, I will add "Date" field in the "Movement" table. This project is only for different products inventory, so I don't have to capture "PartNo".

I want to capture the total number of each product. I plan to do it in Query, by using Sum of Movement "in" less Sum of Movement "out". Then create a report from this Query.

I appy this design from "Order" and "OrderDetail" structure. My purpose is only to know the total number of each Product at the present time. Actually I can meet my target by this mean, but I'm not sure is it a good design for inventory or stock control application.

 

[macca the hacke]

This project is only for different products inventory, so I don't have to capture "PartNo".

But if you don't capture partno in the table called movement, how are you going to know what is being moved in our out?

 

[Steve R.]

Potentially, you need an ACTIONIDNUM or ORDERNUMBER to track incoming and outgoing movements by shipment. It all depends on how complete you want your tracking system to be.

 

[kevin_vdb]

I work at a small belgian company, helping with synthesis and organising the inventory of our chemicals. I made my own database for it. It has functions like adding or removing amounts of a product on date by it's order number, counting the value in stock for each product, and for the whole stock,... I'm just still working on a query that warns me if the amount of a product in stock is lower than requested. If you want to, I can post it to you.

Kevin

 

[dsigner]

I have seen many inventory systems in use (not specifically Access). The most common error with internally developed ones as opposed to bought packages is that they do not record manual stock checks. It is good to track numbers in and out but product does disappear in any warehouse. If you do not manually stockcheck and adjust the programme figures then sooner or later you get an unexpected stockout and the manufacturing line is shut down while someone makes an emergency delivery. It doesn't need to be sophisticated but you should build it in somewhere.

 

[gemma-the-husky]

CHUNNY - a word of warnnig - do not call a column in a table DATE - it probably will be OK, but DATE is a reserved function which returns the current system data - at best it will be confusing - at worse it may cause processing errors - call it trandate, or something similar

 

[neileg]

I would suggest that instead of the movement type being a separate table to identify the ins and outs that you make the quantity in the MovementDetail table +ve or -ve. This way a simple sum of the records will give you the current stock level.

 

[tmd_63]

If you want to use Date in a field, it is better to use the standard database naming conventions, ie for a basic date field the name is dtmDate. This tells you the type of field as well as stopping the system thinking it is a reserved command.