bkapla1
asked on
Best approach to using multiple versions of the same field that is a Lookup Field instead of creating new tables
MS Access 2002
I am taking on someone's existing DB and attempting to improve it.
They have used MANY unncessary lookup fields within the tables in addition to having these fields linked to a lookup table. The issue is that they constantly have multiple versions of the same field, so I'm stumped on the best approach.
Example:
Table: tblHistory
Fields: HistID
Name
Programs1 (LU Field to Programs table, ProgramID is stored with ProgramDesc displayed)
Programs2 (Same as Programs1 field, but stores different value)
Programs3 (Same as Programs1 field, but stores different value)
Programs4 (Same as Programs1 field, but stores different value)
TerritoryState1 (LU Field to State table, StateID is stored with StateDesc displayed)
TerritoryState2 (Same as TerritoryState1, but stores different value)
TerritoryState3 (Same as TerritoryState1, but stores different value)
TerritoryState4 (Same as TerritoryState1, but stores different value)
CandidateType (LU Field to CandidateType Table, CandTypeID is stored with CandTypeDesc displayed)
CandidateType2 (Same as Candidate Type1, but stores different value)
CandidateType3 (Same as Candidate Type1, but stores different value)
CandidateType4 (Same as Candidate Type1, but stores different value)
The issue I am having is that the above table example is present in about 30+ of the tables in the DB such that these tables have 3-5 versions of the values. I know I can break them out so that there can be an intermediary table.
In the example above, for Program, there could be a tblHistProgram table that stores the HistID value And
the ProgramID value. Thus, there would be 4 records in this intermediary table rather than 4 different
fields to hold those values.
However, since there are soo many occurences like this, I'd hate to create so many intermediary tables.
I am looking for any recommendations or to determine if simply using the Lookup Fields in these scenarios is an acceptable way to handle this.
Thanks!
I am taking on someone's existing DB and attempting to improve it.
They have used MANY unncessary lookup fields within the tables in addition to having these fields linked to a lookup table. The issue is that they constantly have multiple versions of the same field, so I'm stumped on the best approach.
Example:
Table: tblHistory
Fields: HistID
Name
Programs1 (LU Field to Programs table, ProgramID is stored with ProgramDesc displayed)
Programs2 (Same as Programs1 field, but stores different value)
Programs3 (Same as Programs1 field, but stores different value)
Programs4 (Same as Programs1 field, but stores different value)
TerritoryState1 (LU Field to State table, StateID is stored with StateDesc displayed)
TerritoryState2 (Same as TerritoryState1, but stores different value)
TerritoryState3 (Same as TerritoryState1, but stores different value)
TerritoryState4 (Same as TerritoryState1, but stores different value)
CandidateType (LU Field to CandidateType Table, CandTypeID is stored with CandTypeDesc displayed)
CandidateType2 (Same as Candidate Type1, but stores different value)
CandidateType3 (Same as Candidate Type1, but stores different value)
CandidateType4 (Same as Candidate Type1, but stores different value)
The issue I am having is that the above table example is present in about 30+ of the tables in the DB such that these tables have 3-5 versions of the values. I know I can break them out so that there can be an intermediary table.
In the example above, for Program, there could be a tblHistProgram table that stores the HistID value And
the ProgramID value. Thus, there would be 4 records in this intermediary table rather than 4 different
fields to hold those values.
However, since there are soo many occurences like this, I'd hate to create so many intermediary tables.
I am looking for any recommendations or to determine if simply using the Lookup Fields in these scenarios is an acceptable way to handle this.
Thanks!
Personally, I do not like lookup tables. That's what forms are for. You should never be altering a table's contents except via a form. In the hands of the unwitting, they are dangerous! Just my 2¢.
ASKER
Imoutwest: To clarify what I mean by "Different Values" means for 1 single HistID record, there will be 4 different Program IDs assigned to that record, 4 different territory states and 4 different Candidate Types.
Sample values of a single record:
HistID: 1
Name: TEMP
Programs1: 100
Programs2: 101
Programs3: 105
Programs4: 106
TerritoryState1: 1
TerritoryState2: 2
TerritoryState3: 5
TerritoryState4: 9
...I think you should get the picture
The other tables are different, but are similar in concept such that they have many fields that are fields 1, 2, 3, 4, etc. that contain different values from a lookup table and are all lookup fields themselves.
So, a table merge wouldn't work since the other tables are different.
Sample values of a single record:
HistID: 1
Name: TEMP
Programs1: 100
Programs2: 101
Programs3: 105
Programs4: 106
TerritoryState1: 1
TerritoryState2: 2
TerritoryState3: 5
TerritoryState4: 9
...I think you should get the picture
The other tables are different, but are similar in concept such that they have many fields that are fields 1, 2, 3, 4, etc. that contain different values from a lookup table and are all lookup fields themselves.
So, a table merge wouldn't work since the other tables are different.
ASKER
GRayL: Agreed as far as using forms for data entry rather than the tables themselves... I think that's exactly why the person that created them used lookup Fields to allow for data entry to the tables themselves.
However, how do you propose to store something like 4 different state values for a single record.
By using a lookup Table vs. a lookup Field, I can store a set # of values for States. This way, if the HistRecord simple stores the "ID" for a state, any change to that state description in the state lookup table will automatically be reflected in the Hist record. The only issue is, I can't have 4 state values joining to a single state lookup table.
This is my dilemma and it exists across NUMEROUS fields and MANY Tables.
However, how do you propose to store something like 4 different state values for a single record.
By using a lookup Table vs. a lookup Field, I can store a set # of values for States. This way, if the HistRecord simple stores the "ID" for a state, any change to that state description in the state lookup table will automatically be reflected in the Hist record. The only issue is, I can't have 4 state values joining to a single state lookup table.
This is my dilemma and it exists across NUMEROUS fields and MANY Tables.
You have a table of States with StateName and StateID
Currently with a Lookuptable, you 'see' the StateName in the Hist table. I say you should only 'see' the StateID. If you want the StateName, use a query:
SELECT a.SomeFld, b.StateName FROM tblHist AS a INNER JOIN tblStates AS b ON a.StateID=b.StateID;
Currently with a Lookuptable, you 'see' the StateName in the Hist table. I say you should only 'see' the StateID. If you want the StateName, use a query:
SELECT a.SomeFld, b.StateName FROM tblHist AS a INNER JOIN tblStates AS b ON a.StateID=b.StateID;
This is just an opinion. I'll see if I can get more views.
perhaps, something to consider
The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm
The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm
I'm curious....and I'm not sure about whether it's just this table....but why 4 ?
Does each column represent an individual or a particular section of some kind of separation?
Does each column represent an individual or a particular section of some kind of separation?
Hello bkapla1,
There are two questions here, and the most important one isn't about lookup fields.
a) lookup fields
In your example, Programs1 stores a key from the table Programs, namely ProgramID. This is very natural and the right approach. There should also be a relationship defined, for efficiency and for integrity.
To edit this value, since the ID is of little or no meaning, the user wants to see the ProgramDescr field instead. So a combo box is created, translating the ID to the description and allowing to select a description to store the corresponding ID.
When the combo is defined within the table structure, this is called a "lookup field". Some programmers don't think the combo should be defined at the table level, but should only be created in the form. That is a matter of opinion, and I will not debate it here. Older versions of Access did not allow this, and most database management systems don't and probably never will. This means you don't need "lookup fields" (in the special sense this expression has for Access) in a database.
So, a lookup field is a quite natural one-to-many relationship, storing a foreign key in a field used to link to another table (even if that other table contains only one description field), with a predefined default combo box in the table structure.
b) the real issue: your table structure
Whenever you see field names with numbers, it means that the table isn't normalized. This creates many problems, for example: find all records for Program 123 (you need to look for that value in four places).
Is there a relation between, say, Programs2, TerritoryState2 and CandidateType2? If yes, you need a structure like:
tblHistory (HistID, other relevant fields)
tblSomething (HistID, Number, Program, TerritoryState, CandidateType)
Where Number is the number of the field, i.e. 2 for the information stored in the field numbered "2" originally, and there are three foreign keys (or "lookup fields") to describe the "Something" this is about (and which I cannot guess).
If not, you need something like this:
tblHistory (HistID, other relevant fields)
tblHistPrograms (HistID, Number, Program)
tblHistTerritories (HistID, Number, State)
tblHistCandidates (HistID, Number, Type)
I suspect that in this case, you can even remove the field Number. Without it, these become very simple relational tables, creating many-to-many relationships between, say, tblHistory and Programs.
In any case, the foreign key can be presented as a combo or not, and defined as a plain number field in the table or as a "lookup field" with a predefined combo. This is irrelevant in the table structure issue.
Finally:
>The only issue is, I can't have 4 state values joining to a single state lookup table.
If you mean you cannot use the same table as lookup for several fields, it's wrong. You can and this is often used in databases.
If you mean you want to get rid of the numbered fields, I'm all with you, but this implies an important structural change, basically meaning a complete rewrite of the database application (all queries, forms and reports will be affected).
Hope this helps!
(°v°)
There are two questions here, and the most important one isn't about lookup fields.
a) lookup fields
In your example, Programs1 stores a key from the table Programs, namely ProgramID. This is very natural and the right approach. There should also be a relationship defined, for efficiency and for integrity.
To edit this value, since the ID is of little or no meaning, the user wants to see the ProgramDescr field instead. So a combo box is created, translating the ID to the description and allowing to select a description to store the corresponding ID.
When the combo is defined within the table structure, this is called a "lookup field". Some programmers don't think the combo should be defined at the table level, but should only be created in the form. That is a matter of opinion, and I will not debate it here. Older versions of Access did not allow this, and most database management systems don't and probably never will. This means you don't need "lookup fields" (in the special sense this expression has for Access) in a database.
So, a lookup field is a quite natural one-to-many relationship, storing a foreign key in a field used to link to another table (even if that other table contains only one description field), with a predefined default combo box in the table structure.
b) the real issue: your table structure
Whenever you see field names with numbers, it means that the table isn't normalized. This creates many problems, for example: find all records for Program 123 (you need to look for that value in four places).
Is there a relation between, say, Programs2, TerritoryState2 and CandidateType2? If yes, you need a structure like:
tblHistory (HistID, other relevant fields)
tblSomething (HistID, Number, Program, TerritoryState, CandidateType)
Where Number is the number of the field, i.e. 2 for the information stored in the field numbered "2" originally, and there are three foreign keys (or "lookup fields") to describe the "Something" this is about (and which I cannot guess).
If not, you need something like this:
tblHistory (HistID, other relevant fields)
tblHistPrograms (HistID, Number, Program)
tblHistTerritories (HistID, Number, State)
tblHistCandidates (HistID, Number, Type)
I suspect that in this case, you can even remove the field Number. Without it, these become very simple relational tables, creating many-to-many relationships between, say, tblHistory and Programs.
In any case, the foreign key can be presented as a combo or not, and defined as a plain number field in the table or as a "lookup field" with a predefined combo. This is irrelevant in the table structure issue.
Finally:
>The only issue is, I can't have 4 state values joining to a single state lookup table.
If you mean you cannot use the same table as lookup for several fields, it's wrong. You can and this is often used in databases.
If you mean you want to get rid of the numbered fields, I'm all with you, but this implies an important structural change, basically meaning a complete rewrite of the database application (all queries, forms and reports will be affected).
Hope this helps!
(°v°)
<<However, since there are soo many occurences like this, I'd hate to create so many intermediary tables.>>
A slightly different approach:
tblLookupTypes - One record per type
LookupTypeID - PK - Autonumber
Description - Text
tblLookupValue - One record per type per value
LookupValueID - PK1 - Autonumber
LookupTypeID - CK1A - FK from table lookup types
LookupValue - CK1B - Text
Description - Text
tblHistory
HistoryID
Name
tblHistoryDetail
HistoryID
LookupValueID - FK to tblLookupValue
Value - Text
Food for thought...
JimD
A slightly different approach:
tblLookupTypes - One record per type
LookupTypeID - PK - Autonumber
Description - Text
tblLookupValue - One record per type per value
LookupValueID - PK1 - Autonumber
LookupTypeID - CK1A - FK from table lookup types
LookupValue - CK1B - Text
Description - Text
tblHistory
HistoryID
Name
tblHistoryDetail
HistoryID
LookupValueID - FK to tblLookupValue
Value - Text
Food for thought...
JimD
ASKER
Harfang,
You definitely are following my dilemma best.
No there is not a relation between the "2nd" values so your 2nd approach is what I was afraid of as a "best practice" solution. I was just hoping for something easier since there are soo many tables with this type of scenario, which ultimately makes the number of new tables requires to support this quite cumbersome.
It's ironic because the client requested Not to have these tables normalized (he claimed that he "used to" certify people in database schemas, which I find hard to believe looking at the mess he created and I'm trying to fix!).
JDettman, I've seen a similar approach in a posting before. I have to say I don't quite follow it, so I'll need some time to absorb it a little, but you're also on the right track.
I think I can take it from here. I'll award the points to both Harfang and JDettman since the fully understand my scenario...I'm not sure how to add points to give you both equal amounts?
You definitely are following my dilemma best.
No there is not a relation between the "2nd" values so your 2nd approach is what I was afraid of as a "best practice" solution. I was just hoping for something easier since there are soo many tables with this type of scenario, which ultimately makes the number of new tables requires to support this quite cumbersome.
It's ironic because the client requested Not to have these tables normalized (he claimed that he "used to" certify people in database schemas, which I find hard to believe looking at the mess he created and I'm trying to fix!).
JDettman, I've seen a similar approach in a posting before. I have to say I don't quite follow it, so I'll need some time to absorb it a little, but you're also on the right track.
I think I can take it from here. I'll award the points to both Harfang and JDettman since the fully understand my scenario...I'm not sure how to add points to give you both equal amounts?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
GrayL... definitely, sorry I didn't realize that your comment meant that you'd find other experts to chime in their opinions.
I see how to split the original points, but is there a way to increase the points that I split above the max 500, so all get more than a split amount (e.g. 500 for each)?
I see how to split the original points, but is there a way to increase the points that I split above the max 500, so all get more than a split amount (e.g. 500 for each)?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW, if you are going to store an actual value in the detail record, you might want to consider storing the type of value allowed in the lookup type table. Also if it's a number you could also do things like min/max of values allowed, can be left blank, etc
JimD
JimD
ASKER
JimD,
Thanks for the addt'l info.
Thanks for the addt'l info.
ASKER
Thanks to all! Since I can't increase the points above 500, I felt it was only fair to award both Harfang and JDettman 200 points each since they contributed the most to my dliemma. I'm still not sure which way I'll go between both of your proposed solutions, but I do know that either way will work well.
I had to save 100 points for GrayL since I wouldn't have heard from either of them if he hadn't reach out to them for their input.
If I could do it my way, I'd award the three of you 500 points, but I guess I can't do that.
Thanks again!!
I had to save 100 points for GrayL since I wouldn't have heard from either of them if he hadn't reach out to them for their input.
If I could do it my way, I'd award the three of you 500 points, but I guess I can't do that.
Thanks again!!
Thanks, glad they could help;-)
Thanks and good luck with your database!
(°v°)
(°v°)
(create a backup first)
If the tables are designed the same then merge all of the Same tables (i.e. Programs1,2,3....) and then use a query for your specific info for your lookups.