allowing multiple choices per row in a lookup column

[Guest]

Hello. So i'm setting up an extensive database for my job that deals with
all the hundreds of programs for all the 50 different affiliates. I've
created databases before, but this one is getting a little out of control
with what kinds of reports and information they want to be able to glean from
the database. The only problem i am having at the moment is trying to figure
out how to select multiple entries for a lookup column. Many of these
program fullfill more than one of the categories listed, and I need to figure
out how to reflect that in the row and table dedicated to each program. I'm
pretty sure this can be done, but I'm either not searching for the right
words under help or I'm totally off base with my approach to setting up this
database. Any assistance or guidance on where I could receive information
about access would be much appreicated. Thanks

 

[Van T. Dinh]

Use another Table tblProgramCategory which is in a One-to-Many relationship
from the tblProgram to store the multiple categories a Program can be
categorized as.

Generally, when I design a database, one of the first things I look at is
what the users need to get out of the database. This way, you can work out
what need to be stored in the database and structure the database
accordingly. Generally, it is a bad idea to design a database without
knowing what the users need to get out of the database.

 

[Guest]

Through previous questioning, i was told to use another table program
category. I thought this made sense, but upon further investigation, i'm
still unsure of where to go from here.... maybe a little more info about my
project will help you understand a little better the info with which I am
working. ...

So all our affiliates provide different programming in four different
program areas. I need to be able to get information about each general
program, all the programs a given affiliate offers, and general information
about the prgrams that fit into each of the four categories (education,
Professional development, cultural access, & public awareness) ...at the
least. I have set up a separate table for each of the four program
categories. In each separate table, I have created spots for each specific
program the affiliate offers...okay so far. columns include ones titled
PRIMARY FOCUS, TARGET POPULATION, OBJECTIVES, WAYS PEOPLE ARE INVOLVED,
PARNERSHIPS, etc. the problem i am having is trying to categorize each
specific program. In most instances, these programs have more than one
objective, focus, or partner. So far, i have created a lookup-column that
contains each choice. In short, I need to be able to choose more than one
option for each look-up column.

I have also created separate tables that contain a list of all these look-up
column choices. I did this originally thinking i would need to create a
separate table for each question. I soon realized that across the four
program categories, the answers would not be similar enough to try to create
relationships to link them together.

So for right now I'm stuck...i thought I was on the right track with the
four separate tables, but maybe not.... If anyone has a suggestion about how
I can create these relationships and/or make it possible to choose more than
one option from my look-up column, I would greatly appreciate it. I've never
created a database with quite so much information or relationships before!
Peace

 

[Douglas J. Steele]

If you're actually using Lookup fields, then no, you're not on the right
track. (see http://www.mvps.org/access/lookupfields.htm at "The Access Web"
for some of the reasons why you shouldn't use Lookup fields)

It sounds as though you've got a many-to-many relationship: each program can
be related to many categories, and each category applies to many programs.
This is implemented by introducing a third intersection table between the
two tables.

Take a look in the Northwind database that comes with Access. The three
tables Products, Orders and Order Details is exactly this setup: each
product can appear on multiple orders, and each order can be for multiple
products, so the Order Details table resolves that many-to-many
relationship.

Take a look at the Orders and Orders Subform forms for one common way of
implementing this in an application.

 

[Guest]

Thanks so much for the info. okay, so no lookup fields...It is true that
there is a many to many relationship. After looking at the Northwind
example, I guess I still don't understand exactly how this example will apply
to my data... In the Northwind example, it seems the bulk of information
would be aligned with the Products table; the complete program list = the
orders table, and the info specific to each program = the order details
table. Is this how I should be looking at it? So I'm thinking one big
table that lists an auto ID, State, program name and which of the four
categories into which the program fits.

I am still having trouble with the program details table...perhaps because
it is just so much information. Each program is looked at in seven different
ways (primary focus, atmosphere of inclusion, partners, ways people are
involved, objectives, evaluation methods, and target populations). As I
mentioned earlier, these can all have multiple choices (and the choices
available are different depending on which of the four categories the program
fits). Can I list all the choices (from all four categories) for primary
focus, etc in one [product] table and just leave it at that? or Does this
then require me to create a separate tables for each of the seven ways to
look at the program? (that is the way I started, but found myself at an
empass with wahat to do next and so much information to handle). It seems
like there could be a much simpler way to set this up.

To further complicate things, I need to not only be able to list the type of
partnership but also how many organizations the affilate partnered with to
carry out its programming. Is this going to require an extra subdatasheet?
is it possible to have more than one sub-sheet/form/etc per record? Any
additional assitance in this matter would be greatly appreciated. Thank you!

 

[Douglas J. Steele]

You could list all of the choices in one table, but indicate which choice(s)
apply to which of the seven different ways, (and even for the different
categories). That way, you can limit each listbox to only those items that
are appropriate. Note that doing this would probably involve a few tables to
model properly.

Realistically, your questions are a little too abstract for me to be able to
give concrete answers.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks so much for the info. okay, so no lookup fields...It is true that
there is a many to many relationship. After looking at the Northwind
example, I guess I still don't understand exactly how this example will
apply
to my data... In the Northwind example, it seems the bulk of information
would be aligned with the Products table; the complete program list = the
orders table, and the info specific to each program = the order details
table. Is this how I should be looking at it? So I'm thinking one big
table that lists an auto ID, State, program name and which of the four
categories into which the program fits.

I am still having trouble with the program details table...perhaps because
it is just so much information. Each program is looked at in seven
different
ways (primary focus, atmosphere of inclusion, partners, ways people are
involved, objectives, evaluation methods, and target populations). As I
mentioned earlier, these can all have multiple choices (and the choices
available are different depending on which of the four categories the
program
fits). Can I list all the choices (from all four categories) for primary
focus, etc in one [product] table and just leave it at that? or Does this
then require me to create a separate tables for each of the seven ways to
look at the program? (that is the way I started, but found myself at an
empass with wahat to do next and so much information to handle). It seems
like there could be a much simpler way to set this up.

To further complicate things, I need to not only be able to list the type
of
partnership but also how many organizations the affilate partnered with to
carry out its programming. Is this going to require an extra
subdatasheet?
is it possible to have more than one sub-sheet/form/etc per record? Any
additional assitance in this matter would be greatly appreciated. Thank
you!

If you're actually using Lookup fields, then no, you're not on the right
track. (see http://www.mvps.org/access/lookupfields.htm at "The Access
Web"
for some of the reasons why you shouldn't use Lookup fields)

It sounds as though you've got a many-to-many relationship: each program
can
be related to many categories, and each category applies to many
programs.
This is implemented by introducing a third intersection table between the
two tables.

Take a look in the Northwind database that comes with Access. The three
tables Products, Orders and Order Details is exactly this setup: each
product can appear on multiple orders, and each order can be for multiple
products, so the Order Details table resolves that many-to-many
relationship.

Take a look at the Orders and Orders Subform forms for one common way of
implementing this in an application.