Generic Description Table

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?

--
Dennis

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.
--
Duane Hookom
Microsoft Access MVP


"Dennis" wrote:

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?

--
Dennis

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

"Dennis" wrote in message
...
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?

--
Dennis

Duane,

I thought about that but suppose you have State and MemTerm (and whatever
else will be in this table) in one table, then what? Or am I missing
something?

--
Gina Whipp

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

http://www.regina-whipp.com/index_files/TipList.htm

"Duane Hookom" wrote in message
...
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.
--
Duane Hookom
Microsoft Access MVP


"Dennis" wrote:

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?

--
Dennis

There is nothing prohibiting you from creating a query with more than one
field linked to copies of the same table.

--
Duane Hookom
Microsoft Access MVP


"Gina Whipp" wrote:

Duane,

I thought about that but suppose you have State and MemTerm (and whatever
else will be in this table) in one table, then what? Or am I missing
something?

--
Gina Whipp

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

http://www.regina-whipp.com/index_files/TipList.htm

"Duane Hookom" wrote in message
...
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.
--
Duane Hookom
Microsoft Access MVP


"Dennis" wrote:

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?

--
Dennis

In a query yes but I was talking about relationships... didn't I say that?
Okay, I see I didn't I need more coffee. Anyway, he asked about
relationships and that's where I saw the problem.

--
Gina Whipp

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

http://www.regina-whipp.com/index_files/TipList.htm

"Duane Hookom" wrote in message
...
There is nothing prohibiting you from creating a query with more than one
field linked to copies of the same table.

--
Duane Hookom
Microsoft Access MVP


"Gina Whipp" wrote:

Duane,

I thought about that but suppose you have State and MemTerm (and whatever
else will be in this table) in one table, then what? Or am I missing
something?

--
Gina Whipp

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

http://www.regina-whipp.com/index_files/TipList.htm

"Duane Hookom" wrote in message
...
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.
--
Duane Hookom
Microsoft Access MVP


"Dennis" wrote:

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?

--
Dennis

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 Hookom
Microsoft Access MVP


"Duane Hookom" wrote:

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.
--
Duane Hookom
Microsoft Access MVP


"Dennis" wrote:

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?

--
Dennis

Duane,

Yep I missed that... thanks for clearing me up.

--
Gina Whipp

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

http://www.regina-whipp.com/index_files/TipList.htm

"Duane Hookom" wrote in message
...
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 Hookom
Microsoft Access MVP


"Duane Hookom" wrote:

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.
--
Duane Hookom
Microsoft Access MVP


"Dennis" wrote:

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?

--
Dennis

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