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: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2017-10-27T13:25:11+00:00

    What does FK stand for? Foreign Key?  I am assuming PK = product key. Just as an add in...i am not familar with stock and inventory terminology. Stock written off? stock-take? I was just told to make an inventory database in Access without every doing this before. So what your saying is that I should still keep the Inventory table but add a colors and sizes table? What would be the difference between the Product table and Invenotry table in your scenario? As well as the difference between the Color and ProductColor tables and Size and ProductSize table?

    I understand what you mean by normalizing it and some of the break down, but I'm not quite grasping the your explainations and reasonings. I would really like to impress my boss and your help is very appreciated.

    The PK Means Primary Key of your table not Product key

    Was this answer helpful?

    0 comments
  2. ScottGem 68,840 Reputation points Volunteer Moderator
    2017-10-27T17:22:35+00:00

    You replied to a 4 year old post that had already been answered.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2018-01-10T16:17:04+00:00

    i have a problem.. what should i do for 1 product that have many suppliers.

    its not easy for me to track inventory for 1 products because i didn't know how to resolve this problems. RM(Ringgit Malaysia)

    Example:

    product name : MATILDA COSMETICS

    price sell : RM 120.00

    supplier A: price RM 57.00

    supplier B: price RM 48.00

    can somebody resolve for me?

    i have many products that need to be check for the stocks but I'm sucks on this..

    Was this answer helpful?

    0 comments
  4. Anonymous
    2018-01-10T17:35:30+00:00

    For future reference, please do not piggy back a question on an old thread, but start a new thread and include a hyperlink to any earlier thread to which you wish to refer.

    Where a product can be supplied by more than one supplier, and a supplier can supply one or more products there is a many-to-many relationship type between products and suppliers.  A many-to-many relationship type is modelled by a table which resolvers the relationship type into two one-to-many relationship types.  In broad outline the tables would be like this:

    Products

    ….ProductID  (PK)

    ….ProductName

    Suppliers

    ….SupplierID  (PK)

    ….Supplier

    ….etc

    And to model the relationship type:

    ProductSuppliers

    ….ProductID  (FK)

    ….SupplierID  (FK)

    ….Price

    The primary key of ProductSuppliers is a composite one of the two foreign key columns.  Price is an attribute of the relationship type, so is a column in this table.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2018-01-10T19:09:26+00:00

    Ok i already try it and its works but i have another questions..how to track the inventory records?

    Can u simplified for my better understanding?

    Was this answer helpful?

    0 comments