CODEWITHSUNDEEP
formsms-accessuser-interfacenormalizationinventory
user2744572
user2744572

Reputation: 21

MS ACCESS-Way to normalize Inventory Data

I have a simple inventory database and I'd like some conceptual help.

Right now I use one table to store all of the items. Here's the problem I run into, Lets say I have 100 of a certain item, Normally I'd just store it as 1 item with quantity being 100. However, if its a high value item with serial numbers, I have to store it 100 times, each with a unique information (serial number, expiration date,Id Number, ETC.) Is there a better way to normalize this? maybe through the use of a another table? My main concern is that when I try to display all the items in a simple datasheet style form I run into huge numbers of these basically duplicate entries, is there a way I might just combine them in a query then have them all display as 1 item with quantity being the number of duplicates then have a sub sheet that I can click to view each serial number, expiration date and etceteria or should I just bite the bullet and leave it be. anyone else have a suggestion for me? Thanks guys.

Upvotes: 0

Views: 533

Answers (1)

Shawn Baker
Shawn Baker

Reputation: 28

I have something similar in a tooling and equipment tracking database. First you are on the right track with having multiple tables. How I have it is one table for the item and one table for the manufacture information. I can have multiple items per a single manufacturer. There is a one to many link between the Manufacturer and the Item.

In your case you would want to have one table for the general item information and one for serial numbers. You could either link them by a unique auto numbered key field or by item number. For ease of use I prefer linking by item number but others would argue that an integer (the auto numbered field) may provide faster results.

As to displaying them you could either create a query that shows them line by line with each serial number or you could do a Form/Sub Form combination to show the single item on the main and the serial numbers on the sub.

Also when you set up the relationships on the table and you open the main item table it will automatically show you that there are linked sub tables and you can expand them to view that data.

I hope this is helpful.

Upvotes: 1

Related Questions