How should i design?

[Greg]

I have a database i would like to design but i'm not sure of how i
should lay the tables out. The easiest way to describe the design is
I have three main fields like
field= Year
field= Make
field= Model

i'm wondering if they should be in the same table or seperate tables
i'm wanting to do combo boxes


that if i select EX
Ford the next combo box would allow me to
select Aerostar; taurus; thunderbird etc...only vehicles linked to the
ford
then the third combo box would allow me to
select 1995; 1996;1997;1998 only years are linked to that model

and then i would have another table linked to that with all the
vehicle information

Many thanks in advance. if you need more information please reply to
newsgroups

Greg

 

[Naresh Nichani MVP]

Hi:

They could be seperate tables --

Table: Model
Fields: ModelID (AutoNumber)
ModelName (Text)

Table: Make
Field: MakeID (AutoNumber)
ModelID (Number) - use type Lookup Wizard to refer to Model
table
Make (Text)

Table Year
Fields: YearID (AutoNumber)
MakeID (Number) - use type Lookup Wizard to refer to Make
table
Year (Text)

Hope this helps

Regards,

Naresh Nichani
Microsoft Access MVP

 

[John Vinson]

I have a database i would like to design but i'm not sure of how i
should lay the tables out. The easiest way to describe the design is
I have three main fields like
field= Year
field= Make
field= Model

i'm wondering if they should be in the same table or seperate tables
i'm wanting to do combo boxes

that if i select EX
Ford the next combo box would allow me to
select Aerostar; taurus; thunderbird etc...only vehicles linked to the
ford
then the third combo box would allow me to
select 1995; 1996;1997;1998 only years are linked to that model

and then i would have another table linked to that with all the
vehicle information

If you're building a database of actual automobiles - so that you
expect to have more than one '97 Ford Aerostar for example - then you
may want two tables:

- Models
Make
Model
Year

Select all three fields and click the Key icon so that they constitute
a joint three-field primary key. Fill this with all the valid
combinations, manually.

The main table

- Autos
AutoID < maybe the VIN, it's a good unique key
Make
Model
ModelYear < all linked to the corresponding fields in Models
<other information about that car>

In your Form (NOT in a table, table datasheets should NEVER have combo
boxes!) you could fill in the three fields based on Queries of the
Models table. Say you have frmCar with combos cboMake, cboModel,
cboYear; the Row Sources for these would be:

cboMake
SELECT DISTINCT Make FROM Models ORDER BY Make;

cboModel
SELECT DISTINCT Model FROM Models
WHERE Models.Make = Forms!frmCar!cboMake
ORDER BY Make;

cboYear
SELECT DISTINCT ModelYear FROM Models
WHERE Models.Make = Forms!frmCar!cboMake
AND Models.Model = Forms!frmCar!cboModel
ORDER BY ModelYear;

You'll need a bit of VBA code in the afterupdate events of cboMake and
cboModel:

Private Sub cboMake_AfterUpdate()
Me!cboModel.Requery
Me!cboYear.Requery
End Sub
Private Sub cboModel_AfterUpdate()
Me!cboYear.Requery
End Sub