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 |
#11
|
|||
|
|||
Generic Description Table
If you don't create a single autonumber primary key on your lookup table, you
can create a multi-field primary key. I think I would add the autonumber and also include a unique index on the type and code fields. If you have an autonumber field in your lookup table, you can just add the lookup table to your report's record source and join the state field in your member table to this field. If you don't mind creating multiple queries, you can create a lookup query for each type like: === qlkpStates ===== SELECT Code as St, Description as State FROM tblLookup WHERE Type = "State"; ================ -- Duane Hookom Microsoft Access MVP "Dennis" wrote: Duane, Gina, My background is medium size systems where it was not uncomment to have about 100 description tables. To make life system, we had a single table with a two part key. I'm starting with a couple of small Access apps, but can already see that are lot of foriegn keys for code descriptions. Yes, I was planning on providing a single form for description code and another one to print them out. The menu that opens the form would pass the type of form (State, Member Term, Member Type, etc) in the opening arguments of the desc form. That way, the fixed part ("STATE", "MEMTYPE") of the key would be hidden from the user. If I had to go to individual tables, I would still use the same approach. Second part of your answer implies something that would kill this approach. In order for this to work, both the Code Name ("STATE") and Code Desc ("FL") must be combined to form the primary key to the Look Up file. However, I'm beginning to get the feeling that Access does not support a primary key that spans two fields. You can set one up in the table properties, so I assumed it would work. However, I'm having trouble with the one table that I'm trying to setup this way. Anyhow, back to my original question. In a report (or a query), assuming a value of "FL" in the state field of a member, how would I retrive the description of "Florida" on an name and address report? I read Duane response, and I understand the form side of the response, but I don't understand how this would apply to reports and queries that are trying to access the description. -- Dennis |
#12
|
|||
|
|||
Generic Description Table
Duane,
Ah, the light bulb is finally on. Let me restate what I think you said. Instead of keying my Look Up table by "State" and "FL", I should key it by an autonumber field and have State and FL as just data fields. The table would look like: tblLookUp Comment / examples autonumber - Primary key CodeType Hard coded text to specify type of code. Code The code itself Code Description The description of the code That data would look like: autonumber Code Type Code Code Description 1 State CA California 2 State FL Florida 3 MemType S Sons 4 MemTerm A Annual. All of my forms would have combo box that would filter by the code type. My reports and user queries would just lookup the foreign key based upon the autonumber and ignore the Code Type. All of my maintenance forms and reports would still work as describe above in a previous response. I would need index the CodeType field with Duplicate = Yes to speed up acces. I could not index the Code field as unique because the codes between two different code types could be the same. L for Life in Member Term and L for Ladies in Member Type. The codes are only unique when the two part key is used as the index. Did I understand you correctly? What a brilliant and elegant solution! If so, what would be the impact of this approach if I switch my database engine to Sql server down the road? Or would I be better to go with a lot of small individual code tables. Most of which would have 3 to 10 rows. To date I have been putting them in Look up field where I type in the list. But I can see this as a huge maintenance problem down the road when someone wants to add an extra code - having to go thought the system and changing all of the places I have put the look up code. What is the best approach for maintainability and ability to switch out the database engine with the minimum amount of work? Thanks again. Dennis |
#13
|
|||
|
|||
Generic Description Table
I think you have a good handle on how I would set this up if I used a single
lookup table. Upsizing to SQL Server would not be an issue. Make sure you create a unique index on the CodeType and Code fields. I never use value lists with combo boxes and never ever use lookup fields defined in tables. -- Duane Hookom Microsoft Access MVP "Dennis" wrote: Duane, Ah, the light bulb is finally on. Let me restate what I think you said. Instead of keying my Look Up table by "State" and "FL", I should key it by an autonumber field and have State and FL as just data fields. The table would look like: tblLookUp Comment / examples autonumber - Primary key CodeType Hard coded text to specify type of code. Code The code itself Code Description The description of the code That data would look like: autonumber Code Type Code Code Description 1 State CA California 2 State FL Florida 3 MemType S Sons 4 MemTerm A Annual. All of my forms would have combo box that would filter by the code type. My reports and user queries would just lookup the foreign key based upon the autonumber and ignore the Code Type. All of my maintenance forms and reports would still work as describe above in a previous response. I would need index the CodeType field with Duplicate = Yes to speed up acces. I could not index the Code field as unique because the codes between two different code types could be the same. L for Life in Member Term and L for Ladies in Member Type. The codes are only unique when the two part key is used as the index. Did I understand you correctly? What a brilliant and elegant solution! If so, what would be the impact of this approach if I switch my database engine to Sql server down the road? Or would I be better to go with a lot of small individual code tables. Most of which would have 3 to 10 rows. To date I have been putting them in Look up field where I type in the list. But I can see this as a huge maintenance problem down the road when someone wants to add an extra code - having to go thought the system and changing all of the places I have put the look up code. What is the best approach for maintainability and ability to switch out the database engine with the minimum amount of work? Thanks again. Dennis |
#14
|
|||
|
|||
Generic Description Table
Duane,
A couple of questions. 1. How do I create a unique index on the "CodeType and Code fields."? I see where you can index a field, but not two fields as a single index. 2. I'm confused by the statement " I never use value lists with combo boxes and never ever use lookup fields defined in tables." How do you have user enters codes such as State or in my case Membership Term of Life, Annual, Semi-Annual, New, Exired, or Honorary? Dennis -- Dennis "Duane Hookom" wrote: I think you have a good handle on how I would set this up if I used a single lookup table. Upsizing to SQL Server would not be an issue. Make sure you create a unique index on the CodeType and Code fields. I never use value lists with combo boxes and never ever use lookup fields defined in tables. -- Duane Hookom Microsoft Access MVP "Dennis" wrote: Duane, Ah, the light bulb is finally on. Let me restate what I think you said. Instead of keying my Look Up table by "State" and "FL", I should key it by an autonumber field and have State and FL as just data fields. The table would look like: tblLookUp Comment / examples autonumber - Primary key CodeType Hard coded text to specify type of code. Code The code itself Code Description The description of the code That data would look like: autonumber Code Type Code Code Description 1 State CA California 2 State FL Florida 3 MemType S Sons 4 MemTerm A Annual. All of my forms would have combo box that would filter by the code type. My reports and user queries would just lookup the foreign key based upon the autonumber and ignore the Code Type. All of my maintenance forms and reports would still work as describe above in a previous response. I would need index the CodeType field with Duplicate = Yes to speed up acces. I could not index the Code field as unique because the codes between two different code types could be the same. L for Life in Member Term and L for Ladies in Member Type. The codes are only unique when the two part key is used as the index. Did I understand you correctly? What a brilliant and elegant solution! If so, what would be the impact of this approach if I switch my database engine to Sql server down the road? Or would I be better to go with a lot of small individual code tables. Most of which would have 3 to 10 rows. To date I have been putting them in Look up field where I type in the list. But I can see this as a huge maintenance problem down the road when someone wants to add an extra code - having to go thought the system and changing all of the places I have put the look up code. What is the best approach for maintainability and ability to switch out the database engine with the minimum amount of work? Thanks again. Dennis |
#15
|
|||
|
|||
Generic Description Table
When in table design, you can open the index dialog. Each index has a name.
You can provide a name for two fields. Index Name Field Name UniTypeCode Type Code I use combo boxes on forms for users to enter looked up values. Users never enter directly into tables. -- Duane Hookom Microsoft Access MVP "Dennis" wrote: Duane, A couple of questions. 1. How do I create a unique index on the "CodeType and Code fields."? I see where you can index a field, but not two fields as a single index. 2. I'm confused by the statement " I never use value lists with combo boxes and never ever use lookup fields defined in tables." How do you have user enters codes such as State or in my case Membership Term of Life, Annual, Semi-Annual, New, Exired, or Honorary? Dennis -- Dennis "Duane Hookom" wrote: I think you have a good handle on how I would set this up if I used a single lookup table. Upsizing to SQL Server would not be an issue. Make sure you create a unique index on the CodeType and Code fields. I never use value lists with combo boxes and never ever use lookup fields defined in tables. -- Duane Hookom Microsoft Access MVP "Dennis" wrote: Duane, Ah, the light bulb is finally on. Let me restate what I think you said. Instead of keying my Look Up table by "State" and "FL", I should key it by an autonumber field and have State and FL as just data fields. The table would look like: tblLookUp Comment / examples autonumber - Primary key CodeType Hard coded text to specify type of code. Code The code itself Code Description The description of the code That data would look like: autonumber Code Type Code Code Description 1 State CA California 2 State FL Florida 3 MemType S Sons 4 MemTerm A Annual. All of my forms would have combo box that would filter by the code type. My reports and user queries would just lookup the foreign key based upon the autonumber and ignore the Code Type. All of my maintenance forms and reports would still work as describe above in a previous response. I would need index the CodeType field with Duplicate = Yes to speed up acces. I could not index the Code field as unique because the codes between two different code types could be the same. L for Life in Member Term and L for Ladies in Member Type. The codes are only unique when the two part key is used as the index. Did I understand you correctly? What a brilliant and elegant solution! If so, what would be the impact of this approach if I switch my database engine to Sql server down the road? Or would I be better to go with a lot of small individual code tables. Most of which would have 3 to 10 rows. To date I have been putting them in Look up field where I type in the list. But I can see this as a huge maintenance problem down the road when someone wants to add an extra code - having to go thought the system and changing all of the places I have put the look up code. What is the best approach for maintainability and ability to switch out the database engine with the minimum amount of work? Thanks again. Dennis |
#16
|
|||
|
|||
Generic Description Table
Duane,
Ok, I found the index dialog. Thanks. I guess I don't know the right terminology to describe what I am doing. But yes, I want to the user to pick their codes from a combo box on forms. The combo box will look up my values from my LookUp tbl. Thank you for that clarification and all of your help. -- Dennis |
|
Thread Tools | |
Display Modes | |
|
|