Lookup Table useage

[ionic-fire via AccessMonster.com]

I have several tables that all have a common field which I would like to use
the same lookup table to be the source of this value because they all stand
for a type of metal that is being used.

Each main record will have a long integer field, and will be related to the
lookup table's pk value. When I went to make relationships on the
Relationships window, each time I made a new relationship (1-to-many) another
copy of the lookup table popped onto the window, delimited by a _1, _2, etc.

Furthermore, when I create queries later on, and I want to find out what
metal that a pk val of, for example, "3" corresponds to, how will I be able
to add the lookup table metal description to the query? I suspect that I need
to use an alias, am I correct?

Thanks a ton!

 

[Jeff Boyce]

How is it that you have "several tables that all have a common field"? That
sounds like having multiple tables that each have a "LastName" field ... but
the tables all contain persons...

To show what metal goes with "3", add the lookup table to the query, join on
that field, and include the "looked up description/name" field in your query
for display. You don't need to show the "3".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I have several tables that all have a common field which I would like to use
the same lookup table to be the source of this value because they all stand
for a type of metal that is being used.

Each main record will have a long integer field, and will be related to the
lookup table's pk value. When I went to make relationships on the
Relationships window, each time I made a new relationship (1-to-many) another
copy of the lookup table popped onto the window, delimited by a _1, _2, etc.

Furthermore, when I create queries later on, and I want to find out what
metal that a pk val of, for example, "3" corresponds to, how will I be able
to add the lookup table metal description to the query? I suspect that I need
to use an alias, am I correct?

Thanks a ton!

First off...

NEVER use Lookup Fields in your tables. Not even oncet.

http://www.mvps.org/access/lookupfields.htm has a critique.

Secondly, never use Tables for data editing or data entry. That's not their
purpose.

Using a Form with combo boxes (based on your lookup field) is fine. That's how
you should interact with data.

You'll get the number-suffixed copies of the lookup table in the relationships
window if you have the same two table related more than once. Relating TableA
to LookupTable and TableB to LookupTable shouldn't cause this effect - could
you explain what your tables are and how they are related?

 

[ionic-fire via AccessMonster.com]

I think I was too vague. I am using forms to enter/modify all of my data.

I have several tables that hold data about different equipment; each
equipment has unique characteristics that are tracked using each table.
However, one unique element is the MOC (material of construction) that the
equipment is made of. Different equipment will have different MOC's. But
since we use the same "pool" of MOC's in all our equipment, I thought it was
best to just have one MOC lookup table. The table for each equipment has a
Long Integer field to store a pk val from the MOC lookup table. Then in my
query later on, I add in the description corresponding to this particular MOC
long integer value. Of course, I have the tables joined in the Relationships
window.

This is where I was getting the multiple tables with an underscore and number
at the table name end. I was trying to use the same table to be the row
source for combo boxes in my form, so that I have only one lookup table.


For example, I have:

tblSiteInfo
tblSiteInfoReactor
tblSiteInfoSeparator

and lookup table is tblMOC_LU

Since there can be multiple reactors and separators in a site, I have a 1:
many relationship set up between tblSiteInfo and tblSiteInfoReactor, as well
as between tblSiteInfo and tblSiteInfoSeparator.

In tblSiteInfoReactor, I have a field called lngzMOCID, and the same for
tblSiteInfoSeparator. On my form, I have a combo box that uses tblMOC_LU as
the row source, and stores the pk val corresponding to a MOC choice into the
appropriate table's current record lngzMOCID field. When I set up
relationships between these tables, that is where tblMOC_LU gets the suffixes
of _1, _2, etc.

Perhaps I was just confused, but I did not think I should have several
different lookup tables, each containing the same data just with a different
table name.

I have several tables that all have a common field which I would like to use
the same lookup table to be the source of this value because they all stand

[quoted text clipped - 11 lines]

Thanks a ton!

First off...

NEVER use Lookup Fields in your tables. Not even oncet.

http://www.mvps.org/access/lookupfields.htm has a critique.

Secondly, never use Tables for data editing or data entry. That's not their
purpose.

Using a Form with combo boxes (based on your lookup field) is fine. That's how
you should interact with data.

You'll get the number-suffixed copies of the lookup table in the relationships
window if you have the same two table related more than once. Relating TableA
to LookupTable and TableB to LookupTable shouldn't cause this effect - could
you explain what your tables are and how they are related?

 

[John W. Vinson]

Perhaps I was just confused, but I did not think I should have several
different lookup tables, each containing the same data just with a different
table name.

Don't worry, you don't.

There's only one table.

It's just displayed repeatedly on the Relationships diagram window, since you
have multiple relationships to the same table.