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

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

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

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

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

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