Another "Simplest" Question

[dvdgaudette]

I'm trying to create an Access database for basic vehicle inventory control at work, something that I can build upon in my spare time, no I'm not the manager.

I have seven tables relating to vehicles (Year, Make, Model, Color, Arrival Date, etc.), all of which are single column tables. I haven't created any relationships because my purpose for these tables is to populate the combo boxes on a form (drop downs for year, make, model, etc.)

This is what I'm trying to do: Open the Inventory Form ---> Use the drop downs to populate the Inventory Table so I can see at a glance what vehicles are at our location.



For now that's all I want to do. Seems so simple, but for some reason I can't get it done and I'm double digit hours into this.

I have the form and combo boxes set up, but when I try to add new records I get numbers in the fields of the Inventory table instead of colors, makes, models, and so on. I believe the numbers I'm getting are the actual sequence number of the data in the combo boxes. For example, beige, black, blue, gold are the 1st, 2nd, 3rd, and 4th colors in the "colors" combo box. When I choose gold, the number 4 displays in the inventory table.

Am I using Access for something it shouln't be used for (no relationships between tables)?

[dvdgaudette]

I just realized the numbers that I'm getting are the actual ID (primary keys).

[orange]

No, but your single column tables are not going to help you, in my view.
I think you should read the following article to get a picture of what a relational database is and some of the underlying concepts.

http://forums.aspfree.com/attachment...achmentid=4712

There are relationships between tables, especially if you're trying to do something with inventory.

You may wish to read this as well- at least the first 3 topics -- work through
Entity relationship Diagramming with your data
http://www.rogersaccesslibrary.com/forum/topic238.html


Also, inventory control/management is not a trivial subject. It is one of the most difficult and companies will often opt for commercial software because of the complexities involved.

[dvdgaudette]

Thanks for the links. I'm starting a database concepts class (IS582) January 4th, but I'd like to get a jump on it. I'll be learning about SQL Server and Oracle, but I would really like to get to know Access along the way.

I realize inventory management requires a substantial amount of expertise, but what I'm doing here is trying to avoid having to go out in three feet of snow and scrape ice off of these vehicles in order to see what is physically there. If I can catch the vehicles as they come in and simply enter the vehicle information via combo boxes (drop downs for year, make, model, color, etc.) I will be much better off.

A simple form with combo boxes that populates a main inventory table would be fine. Ideas?

[orange]

The best advice is to follow the info from Roger's Library on database design; create a list of entities and build a data model; create some test data and test it against your model; reconcile every "difference" and adjust the model or the data as appropriate.

There really are no shortcuts to good table structures. The references I gave are very good.