Generic Description Table

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

All,

I'm using Access 2003 in XP Pro w SP3 on XP Pro SP3. I have many years of
development experience but am VERY new to Access.

Instead of creating multiple description tables for each foreign key, I
would like to have one table structured as follows:

fld 1 = Desc Type - Field would contain the name of the description. For
example, fld1 would contain the text "State", "MemType", "MemTerm".

fld 2 = Code Name - field would contain the foreign key such as "CA", "FL".

fld 3 = Code Description - field would contain the code's full description.

Sample table
State FL Florida
State CA California
State NM New Mexico
MemTerm L Life
MemTerm A Annual
MemTerm SA Semi-Annual

I can setup the table, but how would I setup the relationship with a
"hardcoded" description. For example, in the State Look up, I would need to
hardcode "STATE" in to the Sate Look Up on the forms, queries, and reports.

I'm not sure where to start or is this just a bad idea in Access?
 
All,

I know how to limit what I view in a list box on a form. However, I'm more
concerned how I would retrieve the foreign key description in a query or a
report.
 
You could use a combo box in a state code field with a row source like:

SELECT Code, Description
FROM tblLookups
WHERE Type = "State"
ORDER BY Code;

I like using an autonumber primary key. This might make queries containing
the lookup table updateable.
 
Dennis,

Personally prefer the multiple tables, however to answer your question. You
are going to have to include all three fields in your sombo box and the
'third' field you make not to show (remove check mark) but put 'State' on
the criteria line. Now you will have to do this for each combo box. OR you
could make seperate queries with the DescType in the criteria field but then
you would end up with multiple queries. Actually doing it that way would be
more work then if you just used multiple tables.

Of course, you are now assuming that when ou have to add a new record that
whoever is doing so doesn't make a typo. I also think if you include any
type of File Maintenance forms then this might be confusing to the User,
unless you are always going to be the only one in the database. (Or perhaps
you weren't giving them File Maintenance.) Hmmm, and forget about
relationships, which field would be the Primary Key? Code Name or Code
Description could potentially contain identical information depending on
what's in that table.

You know what... go with the individaul tables! I belive you'll be happier
you did in the long run. But of course, up to you...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
There is nothing prohibiting you from creating a query with more than one
field linked to copies of the same table.
 
Like I stated, I would create a single field, primary key in the lookup table
and then add the table multiple times in the relationship window.
 
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.
 
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,

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,

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
 
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,

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.
 
Back
Top