If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
look ups and tables
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, |
#2
|
|||
|
|||
look ups and tables
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 "Tam" wrote in message ... 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, |
#3
|
|||
|
|||
look ups and tables
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. "Jeanette Cunningham" wrote: 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 "Tam" wrote in message ... 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, |
#4
|
|||
|
|||
look ups and tables
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/sh...ost1784256&Zp= |
Thread Tools | |
Display Modes | |
|
|