Lookup fields from various tables

[MU Eric]

I am building a contact database. I have populated several tables with data
for various types of organizations. I am now ready to begin populating a
table of influential people associated with those organizations, and I am
trying to create a look-up field that selects from the names of the
organizations in the various organizational tables. I have not been able to
figure out how to do this. Thanks in advance for your help!

 

[Jeff Boyce]

Stop now!

Please check the tablesdbdesign newsgroup and mvps.org/access ... the
consensus in both these locations is that lookup fields IN TABLES are a VERY
BAD IDEA. Instead, use a combobox in a form to do lookup.

You ARE using forms, right?!<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MU Eric]

I did more research after posting this and saw the advice against putting
lookup fields in forms. I am using forms, but I still can't seem to figure
out how to accomplish what I want there either. I'm a novice using Access
07...

 

[John W. Vinson]

I did more research after posting this and saw the advice against putting
lookup fields in forms. I am using forms, but I still can't seem to figure
out how to accomplish what I want there either. I'm a novice using Access
07...

Reread your research.

NOBODY objects to using Combo Boxes ("lookups") on Forms. The objections are
all to using Microsoft's misdesigned, misleading, very limited Lookup Wizard
to create Lookup Fields in Tables.

You can open a form in design view; use the Combo Box tool on the Toolbox.
Have the "magic wand" icon selected on the toolbox, and the Combo Box Wizard
will walk you through the steps of creating a Form Combo Box. Note that a
combo box GETS data from one place (normally a query, the combo's row source)
and PUTS data into another place (storing the Bound Column of the combo into
the combo's Control Source field).

 

[MU Eric]

I misspoke. I meant to say that I read the research about putting lookup
fields in tables. I know how to put a combo box in a form. What I am have
difficulty doing is creating a query that will pull all of the names of the
various organizations into one field so that the combo box in the form that
feeds my table of individuals will provide names for all of the organizations
in the organizational tables in one drop-down click.

 

[John W. Vinson]

I misspoke. I meant to say that I read the research about putting lookup
fields in tables. I know how to put a combo box in a form. What I am have
difficulty doing is creating a query that will pull all of the names of the
various organizations into one field so that the combo box in the form that
feeds my table of individuals will provide names for all of the organizations
in the organizational tables in one drop-down click.

If you have one table per organization, with all the tables identically
structured, then your basic table structure needs reconsideration. You should
insteand have ONE members table, with an organization field; or, more likely,
you have a many to many relationship (each Individual can be in zero, one, or
many Organizations and each Organization can have zero, one or many Members).

If you need to pull names from several (similar or identical) tables, you need
a UNION query... of (more likely) a redesign.

Please describe your actual table structure. I may be misreading your post!

 

[MU Eric]

John,

Thanks for your attention. I have one table per type of organization. I
put the types in separate tables because I needed to record different kinds
of information for the various types of organizations. Now what I need to do
is create a table of individual names of contacts. I have created a form for
this table, and I want the form to ask which organization the individual is
affiliated with. I would like for the field in the form to drop down when
clicked with an aggregate list of all of the names of the organizations in
the various organizational tables, preferably in alphabetical order.

I have created and initially populated the tables containing the various
types of organizations. I have created forms for each table, and I am ready
to start populating the table of individual people.

Hopefully this provides enough information. I work for a non-profit that
provides enrichment programs for employees and leaders in various other
non-profit organizations of varying characteristics. I am keeping the
organizations in separate tables so that we can track the types of
organizations that are most utilizing our services, and a separate list of
individuals so that I can formulate and track our contact and promotional
strategies.

 

[John W. Vinson]

John,

Thanks for your attention. I have one table per type of organization. I
put the types in separate tables because I needed to record different kinds
of information for the various types of organizations. Now what I need to do
is create a table of individual names of contacts. I have created a form for
this table, and I want the form to ask which organization the individual is
affiliated with. I would like for the field in the form to drop down when
clicked with an aggregate list of all of the names of the organizations in
the various organizational tables, preferably in alphabetical order.

I have created and initially populated the tables containing the various
types of organizations. I have created forms for each table, and I am ready
to start populating the table of individual people.

Hopefully this provides enough information. I work for a non-profit that
provides enrichment programs for employees and leaders in various other
non-profit organizations of varying characteristics. I am keeping the
organizations in separate tables so that we can track the types of
organizations that are most utilizing our services, and a separate list of
individuals so that I can formulate and track our contact and promotional
strategies.

Again... I cannot see your tables. It sounds like you have a case of
"Subclassing" (where you have a "superclass" of all Organizations, and each
Organization belongs to one or more "subclasses" of specific types of
organizations); this is one of the rather infrequent cases where one to one
relationships may be appropriate.

Do you have a table of Organizations, containing all of them?
Do you have a table of Organization Types?
Can an individual contact belong to one and only one Organization, or may they
belong to several?
How are you (or ARE you!?) currently entering people's names?
When you select an organization from this alphabetized combo box, what do you
want done with that choice?