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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2013-04-10T20:53:45+00:00

    I think we've addressed your original post by giving you a robust model as a basis for your application.  The form I included is a very basic interface, for inserting inventory data, but would be better implemented as a continuous subform within a products form, which would also include subforms for suppliers, colours and sizes.  I'll try and amend it and post back when I've done so.

    You will need to add a form for inserting supplier data, and at present the model does not cater for events at all.  You'll find a ready source of support here as you develop the application.  However, I think you would be well advised to first invest some time in learning the basics of application development in Access.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2013-04-10T19:58:27+00:00

    Never mind about where to find the code. I took a computer science class once and it did not go well, so looking at this scares me. What if I ever need to adjust it or take something out or add something?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2013-04-10T19:54:32+00:00

    That looks like it makes much more sense now. Thats exactly what I was trying to do but had trouble formatting it. Can I now enter more inventory records? Would I just enter them as I did before? Or do I now use the frmInventory? I didn't put all of the inventory in because I wanted to test everything before I got too far and have to start over again. From what your saying I should from now on use the frmInventory to add the rest, correct?

    How do you look at the 'code'?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2013-04-10T17:40:36+00:00

    I've uploaded an amended version of your file as MaryKelly.mdb to my public databases folder at:

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

    Take a look first at the relationships window to see how the tables fit together.  I had to delete all the existing inventory records first and update one or two other tables before I could enforce the relationships, but all of the products are still in there.

    I've added a little form for inserting inventory data, with an unbound combo box at the top to navigate to a product, and bound controls below to enter the data.  The colour and size combo boxes are correlated with the product combo box by referencing it in their RowSource properties, so they only show the colours and sizes applicable to the selected product, so you'll need to have the relevant rows in the ProductColors and ProductSizes tables first if they are not already there, for which you can set up a simple products form with colours and size subforms containing combo boxes to select however many colours and sizes apply to each product.

    Take a look at the code in the form's module to see how the combo boxes are requeried in the relevant event procedures to restrict them to the colours and sizes for the current product.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2013-04-10T15:51:31+00:00

    Was this answer helpful?

    0 comments