look ups and tables

[Tam]

Research has led me to understand that look ups do not belong on tables. Does
that mean that the fields I was going to look up should be removed from the
table as well?

tblDrivers
DriverID - PK
DLNumber- text
DLExpiryDate - date/time
DLStatus - UNKNOWN:There is 1 of 4 choices: Active, Conditional, Interim,
Suspended.
DLProvinceOfIssue - text
DLClasses Held - UNKNOWN: 7 classes, more than one/ driver-unsure how to
proceed.
DriverAbstract - link
AbstractDate - date/time
AbsrtactExpiryDate - date/time
AbstractReviewer - text

1. Do I leave those 2 fields in the table without a look up?
2. Do I create a DLStatus table and a DLClass table?
3. Do I remove the 2 fields from the table altogether and not worry about
them until designing the form?
4.None of the above


thanks,

 

[Jeanette Cunningham]

Hi Tam,

create a table for the DLStatus. I will call it tlkpDLStatus for now.
Give it one field called DLStatusID - a text field.
Make that field the primary key.

In tblDrivers change the field DLStatus to DLStatusID and make it a text
field.

Open the relationships window, show tblDrivers and tlkpDLStatus.
Drag the field called DLStatusID from tlkpDLStatus over and onto the field
called DLStatusID in tblDrivers.
In the dialog that opens, choose the option to create referential integrity.

You will need a separate table for DLClasses Held.
You will need a one to many relationship between tblDrivers and
tblDLClasses.
tblDLClasses will need a primary key field and a field for DriverID which is
related to the DriverID in tblDrivers.
You will have other info in tblDLClasses such as date of each class, put
that in a field called ClassDate and make it a date-time data type.
This table will show one record for every DLClass taken by each driver.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Dale Fye]

Jeanette,

I know this is probably splitting hairs, but why would you store the text
value in both tables? Why not create tlkbDLStatus with an DLStatusID
(numeric) and DLStatusText (text). Then store the numeric DLStatusID value
in tblDrivers.

Maybe I'm just paranoid, but in the past, I've had clients change their mind
about what they wanted to call something (text), so I almost always use a
numeric ID value as well as a text value in my lookup tables. This makes it
so much easier to change "Conditional" to "Probationary", or whatever,
because I only have to change in in the lookup table.

Also, depending on the length of the text, and the number of records in the
db, you can save quite a bit of space by using an Integer rather than a
string as the datatype of the field in the main table.

--
Dale

email address is invalid
Please reply to newsgroup only.

 

[Tam]

Jeanette and Dale.
Thank you both for your help...

I'll admit to that 'caught with the hand in the cookie jar' feeling as I
also asked this question over at UtterAccess, where I have uploaded part of
the DB I'm working on and have received the same great advice there.

In case you're curious I've pasted the link to my post over there.
Thanks again,
Tam



http://www.utteraccess.com/forums/s...=151949&Zd=l&Zn=&Zt=5&Zs=&Zy=#Post1784256&Zp=