Should this be two tables?

[Ed from AZ]

I have a two-column table listing vehicles by type and serial number.
The type is definitely repeated; the serial number may possibly be
repeated.

I wanted to create a form to allow users to add a new vehicle to this
table. I wanted a control that would contain all the unique values
from the VehType column or allow the user to enter a new type. There
will also be a Data Entry form, which will also contain a drop-down
for vehicle type, as well as a drop-down for vehicle serial number.
(Hopefully, the SN control will only contain the SN for that vehicle
type - but I'll get there!)

That got me wondering if this would be easier to manage if I had a
separate table for Vehicle Type, where the type would only be entered
once. My drop-down control would pull straight from here, rather than
requiring contraints for unique values only.

As you can probably tell, I don't know much about what I'm doing
here. Any pointers are helpful.

Ed

 

[Roger Carlson]

Yes, you should have a separate table holding the Type. I prefer to add an
automnumber primary key to my lookup tables. So I have this:

tblVehicleType
VehicleTypeID (pk)
VehicleType

Then your two-column table would store the VehicleTypeID instead of the
actual Type. Then I'd create a relationship between the two tables.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



The combobox control would show the Type, but would store the TypeID

 

[Fred]

You would be better off with two main tables...a tables of vehicles and a
table of vehicle types. And linking a PK in the vehicle type table to a FK
in the vehicle table.