First Timer: Product Inventory Table

Anonymous
2013-04-03T18:19:00+00:00

Creating a Product Inventory database to keep track of all the promotional items my company has. So far I have three tables: Suppliers, Inventory, and Event. In my Inventory table I have the following columns (in order): Product ID, Product Name, Description, Logo, Color, Size, Total Quantity, Supplier, Event, and Storage. There are so many different styles of shirts that we have accumulated over the years that for every shirt style there are multiple sizes and sometimes multiple colors. So with that, I have choosen to add the size and/or color to the product name as well as in the size and color columns.

For example:

Men's Wicking Polo: Small                             S

Men's Wicking Polo: Medium                        M

Blue Crinkle Cut Paper                                           Blue

Purple Crinkle Cut Paper                                        Purple

Will this create a problem? I didn't know how else to set it up because it didn't think the database would recognize that five Crinkle Cut Papers are different colors (making it five individual products because of the colors), if the product name on all of them is the same.

One more: I tried to run some relationships like between the Suppliers table and the suppliers column under Inventory but it didn't work.

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

46 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-01-10T21:38:11+00:00

    I'd need to know more about the structure of your database overall, and how you wish to manage inventory.  Does your database include purchase and sales orders for instance, and if so how are they modelled?  Do you wish to treat each product, regardless of supplier, as a single inventory category, or do you wish to maintain separate inventory data per supplier for each product supplied by more than one supplier?

    A fully developed inventory management database is a complex affair, and there are commercial inventory management products available whose functionality would take a lot of time and effort to reproduce in an Access application, even if you have the skills to do so.  Unless you only require a simplified system with limited functionality, purchasing a commercial application will often be a more sensible business option than trying to reinvent the wheel.

    You'll find an example which illustrates the basic methodologies of a simple inventory management database as Inventory.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file is not intended to be a working application, however, but only a starting point to provide some basic guidance in what would be involved in the development of an operational database.  It does not include purchase orders, only sales orders, and treats stock acquisition generically, with no supplier data whatsoever.  Like all my demos, it assumes that anyone consulting it will have sufficient technical knowledge of Access development to be able to apply and adapt the methodologies to their own requirements.

    Was this answer helpful?

    0 comments