Car Database Need Help!!!!

[AccessBeginner1]

Hi Everyone

I am trying to solve problem which keep disturbing me for a few days. I am trying to create a database with list of Makes and Models. What I want from this database is when I select for example Toyota the range of only Toyota Models appear (Corolla, Prius, RAV4 etc). So far I got tblVehilcles (VehicleID, Make, Model, Year etc), tblMake(MakeID, Make), tblModel(ModelID, Make, Model).

Make:
MakeID - PK
Make

Model:
ModelID - PK
Make - FK
Model

Vehicles:
VehicleID -PK
Make
Model - FK
Year

Do I need to create junction table or something? Please help, preferably in details as I am new to access!!!! :banghead::banghead::banghead::banghead::banghead::banghead::banghead::banghead::banghead::banghead:

 

[RobTuby]

try this as an example

 

[plog]

What I want from this database is when I select for example Toyota the range of only Toyota Models appear

This is a form issue. Search this forum for 'cascading combo boxes' or 'cascading drop down'. Its been solved a bunch of times before and will get you on the right track.

With that said, your Vehicles table is incorrect, you are storing redundant data. If you have a Make value attached to a model (as you do in the Model table), then you do not store the Make value in the Vehicle table. You already know what Make, because you can find it by querying the Model table.

 

[jdraw]

See these free videos from DataPig
http://www.datapigtechnologies.com/flashfiles/combobox1.html
http://www.datapigtechnologies.com/flashfiles/combobox2.html

They are a little older, but the concepts and presentation are quite well done.
Good luck.

 

[AccessBeginner1]

Very big thank you to all you guys!!!! It sounds like exactly what I was looking for, will try to implement it in my project

 

[AccessBeginner1]

Hi guys

Got another trouble, when I create a query from tblVehicles and want to display Make and Model (Volvo S40) in one, the query only shows referential ID number as the values from tblVehicle stored as numeric. Could you please help how can I get around?:banghead:

 

[jdraw]

Please show us a jpg of your tables or better yet post a copy of your database.
You will have to ZIP it because of you post count.

 

[smig]

I suggest you need to redesign your tables:

Make:
MakeID - PK
Make

Model:
ModelID - PK
MakeID - FK
Model

Vehicles:
VehicleID -PK
MakeID (This is not required as the Maker is known by the Model)
ModelID - FK
Year

 

[MarlaC]

Hi guys

Got another trouble, when I create a query from tblVehicles and want to display Make and Model (Volvo S40) in one, the query only shows referential ID number as the values from tblVehicle stored as numeric. Could you please help how can I get around?:banghead:

You need to link the tblVehicles to the tblModels by ModelID in your query, then link tblModels to tblMake by MakeID. Once you have done that, add the Model text field from tblModels and the Make text field from tblMake to your query grid to display when you run the query. You can deselect the numeric values from displaying unless you wish to see tyhem.