Access Database Creation Questions

[Spaceninja]

I am playing around with Access to create a database for keeping track of some various collections I have. I have created the tables that I need and now I am trying to group tables together for the front end. I'm not exactly sure how to do this or if I am looking in the right place or what to call what I want to do. For instance I have Different colors as my tables, Green, Blue, Black etc. Now I want to create another table that would be above that for a drop down menu called Colors that would reference those tables and pull up individual data.

Colors
Blue
Black
Green

I think knowing how to make that work would get me started in the right direction. I will be editing the final tables as those are the only ones that contain any useful data.

Should I even use Access for this or go a different route all together?

 

[Spaceninja]

I'll do my best to explain. So I have a table called Green, Field one are different shades of green. I have another table called Blue and Field one on it has different shades of blue and so on with other colors. In the Form I want to put in the name of the Item, lets say a rock. I give it a name, then choose the color, say Green in the drop down. I want the second drop down to be tied to the first so I get the different shades of Green not the shades of Blue. If I choose Yellow I get the different shades of Yellow.

Hopefully that explains what I want to do.

 

[modi123]

No clue on what your collections are, but typically you want to organize your tables in a relational way.

So you would have one table for your item. This has a unique ID which you would make your key. Perhaps it has a name field, a date entered field, and value.

From there you would have a table of colors. Not many tables of colors, but one table of colors. This holds your color info, hex codes, info about the color source/manufacturer/etc. It also has a key. If you need to add a column for the 'general color' then you can do that.

Now.. if you believe you can assign only one color to one item that is called a 'one to one' relationship. You would have a column in your item table that holds the key (the id) for the color colum.

If you believe you can have many colors to one item then you'll need a third table that contains two columns - one for the item id and one for the color id.

All of this breaks it up for better storage and then, ultimately, for the use of joins to query information.