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
    2013-04-08T18:44:59+00:00

    Alright! This is great. 

    I now have my Products table with ProductId(PK), ProductName, and SupplierID. and I have my Suppliers table. I am reading about junction tables and it says 'it's a special table that keeps track of related record in two other tables". I am assuming that the "other two tables" would not mean my Suppliers and Product table, but the Product table and another table. Am I assuming correctly? If so which table would I put my second SupplierID field?

    Was this answer helpful?

    0 comments
  2. ScottGem 68,840 Reputation points Volunteer Moderator
    2013-04-08T19:45:04+00:00

    Sorry you assume wrong. Ken and I have both tried to explain this. A junction table models the many to many relationship. It does this by creating 2 one to many relations in the same table. So the junction table would look like this:

    tblProductSuppliers

    ProductSupplerID (PK Autonumber This is optional)

    ProductID (FK)

    SupplierID (FK)

    So you would NOT have SupplierID in your Product table, nor would you have ProductID in your Supplier table. You would have one record in tblProductSuppliers for each supplier for each Product.

    Lets say you get short sleeve polo shirts from Lands End as well as a local vendor. You would then have 2 records for polo shirts in the junction table, for each supplier.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-04-08T19:55:54+00:00

    Ohhh I see. I was thinking of it the wrong way. Thank you for correcting me.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-04-08T20:32:28+00:00

    To see an example of such a many-to-many relationship type and how to interface with the tables via a form/subform take a look at ParentActivities.zip in my public databases folder at:

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

    If you open the parents form you'll see that you can enter multiple activities per parent into the subform.  If you want to enter a new activity not already represented in the Activities table you can type the new activity name into the combo box in the subform and it will, after user confirmation, be inserted as a new row into the activities table.  This is done by code in the subform's NotInList event procedure.

    For examples of the use of the NotInList event procedure in other contexts take a look at NotInList.zip in the same SkyDrive folder.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2013-04-10T14:30:12+00:00

    I took a look at your databases. I see how you made the relatioships. I started making mine but of course came across some that wouldn't make a connection. I took a snapshot and want you to look at it, but this does not allow me to upload any files. Any recommendation?

    Was this answer helpful?

    0 comments