Lookup Evils

I have been reading all about the evils of lookups in tables, however could
someone please explain how you avoid using them - is the answer to have many
tiny tables with potentially only 2 fields (assuming it is necessary to have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have several
fields which have 2 or sometimes 3 options and it seems a lot to create a
table for this.

Many thanks

Helen

Lookup fields, not lookup tables.

If you are fairly certain you will never need to expand the list of possible
values beyond two records, you can use a Value List row source in the combo
(or list) box on your form. A reminder: Access tables store data, Access
forms display it (and allow for data entry/edit).

.... and the first time you go back to modify a combo box's value list
because you/your customer added a third choice, you'll reconsider using a
table!

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/


"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however
could
someone please explain how you avoid using them - is the answer to have
many
tiny tables with potentially only 2 fields (assuming it is necessary to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have several
fields which have 2 or sometimes 3 options and it seems a lot to create a
table for this.

Many thanks

Helen

To expand just a bit on what Jeff wrote, if you have a field into which you
can put one of just a few words or phrases then you can bind a combo box to
that field, and make the combo box row source a Value List. If you have a
longer list (states or departments or whatever), and especially if you have
a list that could one day change, then you may do better to make a table
containing just those values, and to use that table as the combo box row
source. This is an entirely different matter than using a lookup field in a
table. Lookup tables are fine. There is a big difference between a lookup
field and a lookup table that is used for populating a field.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however could
someone please explain how you avoid using them - is the answer to have
many
tiny tables with potentially only 2 fields (assuming it is necessary to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have several
fields which have 2 or sometimes 3 options and it seems a lot to create a
table for this.

Many thanks

Helen

I (think!) I am clear what a look up table is - but is it a good idea to have
a primary key in the lookup table? Or could it just be a single field with
the list of items required to populate the drop down?

I have in the past found that when I have used a value list on a field in a
table (I assume this is what you mean by a lookup field) if you then need to
alter the values (yes OK you start to win your point here!) the there is no
point in altering them in the table because the form doesn't update.

Thanks again

Helen

"BruceM" wrote:

To expand just a bit on what Jeff wrote, if you have a field into which you
can put one of just a few words or phrases then you can bind a combo box to
that field, and make the combo box row source a Value List. If you have a
longer list (states or departments or whatever), and especially if you have
a list that could one day change, then you may do better to make a table
containing just those values, and to use that table as the combo box row
source. This is an entirely different matter than using a lookup field in a
table. Lookup tables are fine. There is a big difference between a lookup
field and a lookup table that is used for populating a field.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however could
someone please explain how you avoid using them - is the answer to have
many
tiny tables with potentially only 2 fields (assuming it is necessary to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have several
fields which have 2 or sometimes 3 options and it seems a lot to create a
table for this.

Many thanks

Helen

I don't think there is really a downside to adding a primary key (PK) field.
If your table is a list of 50 states in the U.S.A., and if the state name is
all you are storing, then it probably doesn't matter if you have a PK, but
it won't hurt either, and you may find you need it later. I'll just add
here that my opinion is that if you are storing a single field it may as
well be the actual value (e.g. the state name) as a PK in a related table.
However, if you are storing the state name and an abbreviation (and the
state flower or whatever), then you should probably store the PK, and just
display the other fields without storing them.

There is no single answer to your question about whether to have a PK. We
have a situation where I work in which the person's name has to appear as it
was when a particular type of document was created, so I have to store the
actual name. If I stored the PK, then when a person's name changes the old
records will show the new name, which is not acceptable. If on the other
hand you are keeping training records, you probably want old training
records to show the person's current name. In that case the PK must be
stored. For another example, consider payroll. A person whose name changes
receives a W-2 form for the new name, even if some of the paychecks were
made out to the former name. I'm not trying to win a point, but rather to
suggest you consider the effect on old data of a change to something like a
person's name (or a country name, for that matter), which is subject to
possible change.

A value list IS NOT the same as a lookup field. A value list may be
something like Yes, No, Maybe. You create it by selecting Value List as the
Row Source Type (in the combo box properties), and by adding Yes;No;Maybe to
the Row Source. This is OK for a limited list. If you need to add
"Probably" to the list you can insert that pretty easily into the row
source. However, if it is a list that is subject to change it is often
easier to use a table or query as the row source.

A lookup field is created in table design view by selecting Insert Lookup
Field. This is the thing that experienced developers urge you to avoid.
The reasons have been discussed in numerous posts and articles. I have
never used a lookup field, so have not experienced first-hand the problems
that can result.


"HelenJ" wrote in message
...
I (think!) I am clear what a look up table is - but is it a good idea to
have
a primary key in the lookup table? Or could it just be a single field
with
the list of items required to populate the drop down?

I have in the past found that when I have used a value list on a field in
a
table (I assume this is what you mean by a lookup field) if you then need
to
alter the values (yes OK you start to win your point here!) the there is
no
point in altering them in the table because the form doesn't update.

Thanks again

Helen

"BruceM" wrote:

To expand just a bit on what Jeff wrote, if you have a field into which
you
can put one of just a few words or phrases then you can bind a combo box
to
that field, and make the combo box row source a Value List. If you have
a
longer list (states or departments or whatever), and especially if you
have
a list that could one day change, then you may do better to make a table
containing just those values, and to use that table as the combo box row
source. This is an entirely different matter than using a lookup field
in a
table. Lookup tables are fine. There is a big difference between a
lookup
field and a lookup table that is used for populating a field.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however
could
someone please explain how you avoid using them - is the answer to have
many
tiny tables with potentially only 2 fields (assuming it is necessary to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have
several
fields which have 2 or sometimes 3 options and it seems a lot to create
a
table for this.

Many thanks

Helen

Thanks for your comprehensive answer Bruce. I think I will stick with PKs
since as you say if you change a value then it will change everywhere - which
is what I want in this case.

As for the lookups - I must confess that I am still a bit confused - if you
use insert Lookup field - this seems to be the same as using the lookup
wizard and if you choose to type in "the values that I want" then this
creates a value list. Or is it that at this stage you choose to use more
than one column? Not something that I have ever done either!

I will carry on in happy designing mode :-)

Many thanks
"BruceM" wrote:

I don't think there is really a downside to adding a primary key (PK) field.
If your table is a list of 50 states in the U.S.A., and if the state name is
all you are storing, then it probably doesn't matter if you have a PK, but
it won't hurt either, and you may find you need it later. I'll just add
here that my opinion is that if you are storing a single field it may as
well be the actual value (e.g. the state name) as a PK in a related table.
However, if you are storing the state name and an abbreviation (and the
state flower or whatever), then you should probably store the PK, and just
display the other fields without storing them.

There is no single answer to your question about whether to have a PK. We
have a situation where I work in which the person's name has to appear as it
was when a particular type of document was created, so I have to store the
actual name. If I stored the PK, then when a person's name changes the old
records will show the new name, which is not acceptable. If on the other
hand you are keeping training records, you probably want old training
records to show the person's current name. In that case the PK must be
stored. For another example, consider payroll. A person whose name changes
receives a W-2 form for the new name, even if some of the paychecks were
made out to the former name. I'm not trying to win a point, but rather to
suggest you consider the effect on old data of a change to something like a
person's name (or a country name, for that matter), which is subject to
possible change.

A value list IS NOT the same as a lookup field. A value list may be
something like Yes, No, Maybe. You create it by selecting Value List as the
Row Source Type (in the combo box properties), and by adding Yes;No;Maybe to
the Row Source. This is OK for a limited list. If you need to add
"Probably" to the list you can insert that pretty easily into the row
source. However, if it is a list that is subject to change it is often
easier to use a table or query as the row source.

A lookup field is created in table design view by selecting Insert Lookup
Field. This is the thing that experienced developers urge you to avoid.
The reasons have been discussed in numerous posts and articles. I have
never used a lookup field, so have not experienced first-hand the problems
that can result.


"HelenJ" wrote in message
...
I (think!) I am clear what a look up table is - but is it a good idea to
have
a primary key in the lookup table? Or could it just be a single field
with
the list of items required to populate the drop down?

I have in the past found that when I have used a value list on a field in
a
table (I assume this is what you mean by a lookup field) if you then need
to
alter the values (yes OK you start to win your point here!) the there is
no
point in altering them in the table because the form doesn't update.

Thanks again

Helen

"BruceM" wrote:

To expand just a bit on what Jeff wrote, if you have a field into which
you
can put one of just a few words or phrases then you can bind a combo box
to
that field, and make the combo box row source a Value List. If you have
a
longer list (states or departments or whatever), and especially if you
have
a list that could one day change, then you may do better to make a table
containing just those values, and to use that table as the combo box row
source. This is an entirely different matter than using a lookup field
in a
table. Lookup tables are fine. There is a big difference between a
lookup
field and a lookup table that is used for populating a field.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however
could
someone please explain how you avoid using them - is the answer to have
many
tiny tables with potentially only 2 fields (assuming it is necessary to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have
several
fields which have 2 or sometimes 3 options and it seems a lot to create
a
table for this.

Many thanks

Helen

Yes, Insert Lookup Field is the same as using the Lookup Wizard. The
point that the Access MVPs and others have been making is that using a
lookup FIELD in a table can lead to problems. For one thing, it is
difficult if not impossible to change a lookup field to a conventional
field.
Let's say you have a table (tblStatus) for creating status reports, with
information about sales, inventory, etc. If you have a table field for
Department, just make it a text field in the table. On the form, create a
combo box that has Department as its record source. You have two options
now. You can select Value List as the Row Source Type, then in Row Source
type in a list of departments separated by semi-colons. Or you can create a
department table (tblDept), then create a query (qryDept) based on that
table. Using the query will let you specify a sort order. In combo box
properties, select Table/Query as the Row Source Type, then select qryDept
as the Row Source. If tblDept has a PK and a department name, and qryDept
has the same (in that order), you would set your column count to 2, the
column widths to 0";1", and the bound column to 1 if you want to view the
department name and store the PK. If you want to store the department name
itself you would set the bound column to 2. You could also just leave PK
out of qryDept. When you create a combo box its row source and column count
default to 1, and the bound column defaults to 1.
Note that I am describing the use of tools at the user interface level (the
form) to facilitate data entry. This is the standard and time-tested
approach. People sometimes disagree about details, but not about the
concept. In this example, tblDept functions as a lookup table that supplies
values for the Department field in tblStatus. There is no need for user
interface tools at the table level, since users should not be entering or
changing data except through forms. If you create your lookups at the form
level (using combo boxes as I have described, or list boxes), lookup fields
will never be an issue.
In case you have not seen this article, it provides a succinct summary of
the problems with lookup FIELDS in tables.
http://www.mvps.org/access/lookupfields.htm

"HelenJ" wrote in message
...
Thanks for your comprehensive answer Bruce. I think I will stick with PKs
since as you say if you change a value then it will change everywhere -
which
is what I want in this case.

As for the lookups - I must confess that I am still a bit confused - if
you
use insert Lookup field - this seems to be the same as using the lookup
wizard and if you choose to type in "the values that I want" then this
creates a value list. Or is it that at this stage you choose to use more
than one column? Not something that I have ever done either!

I will carry on in happy designing mode :-)

Many thanks
"BruceM" wrote:

I don't think there is really a downside to adding a primary key (PK)
field.
If your table is a list of 50 states in the U.S.A., and if the state name
is
all you are storing, then it probably doesn't matter if you have a PK,
but
it won't hurt either, and you may find you need it later. I'll just add
here that my opinion is that if you are storing a single field it may as
well be the actual value (e.g. the state name) as a PK in a related
table.
However, if you are storing the state name and an abbreviation (and the
state flower or whatever), then you should probably store the PK, and
just
display the other fields without storing them.

There is no single answer to your question about whether to have a PK.
We
have a situation where I work in which the person's name has to appear as
it
was when a particular type of document was created, so I have to store
the
actual name. If I stored the PK, then when a person's name changes the
old
records will show the new name, which is not acceptable. If on the other
hand you are keeping training records, you probably want old training
records to show the person's current name. In that case the PK must be
stored. For another example, consider payroll. A person whose name
changes
receives a W-2 form for the new name, even if some of the paychecks were
made out to the former name. I'm not trying to win a point, but rather
to
suggest you consider the effect on old data of a change to something like
a
person's name (or a country name, for that matter), which is subject to
possible change.

A value list IS NOT the same as a lookup field. A value list may be
something like Yes, No, Maybe. You create it by selecting Value List as
the
Row Source Type (in the combo box properties), and by adding Yes;No;Maybe
to
the Row Source. This is OK for a limited list. If you need to add
"Probably" to the list you can insert that pretty easily into the row
source. However, if it is a list that is subject to change it is often
easier to use a table or query as the row source.

A lookup field is created in table design view by selecting Insert
Lookup
Field. This is the thing that experienced developers urge you to avoid.
The reasons have been discussed in numerous posts and articles. I have
never used a lookup field, so have not experienced first-hand the
problems
that can result.


"HelenJ" wrote in message
...
I (think!) I am clear what a look up table is - but is it a good idea to
have
a primary key in the lookup table? Or could it just be a single field
with
the list of items required to populate the drop down?

I have in the past found that when I have used a value list on a field
in
a
table (I assume this is what you mean by a lookup field) if you then
need
to
alter the values (yes OK you start to win your point here!) the there
is
no
point in altering them in the table because the form doesn't update.

Thanks again

Helen

"BruceM" wrote:

To expand just a bit on what Jeff wrote, if you have a field into
which
you
can put one of just a few words or phrases then you can bind a combo
box
to
that field, and make the combo box row source a Value List. If you
have
a
longer list (states or departments or whatever), and especially if you
have
a list that could one day change, then you may do better to make a
table
containing just those values, and to use that table as the combo box
row
source. This is an entirely different matter than using a lookup
field
in a
table. Lookup tables are fine. There is a big difference between a
lookup
field and a lookup table that is used for populating a field.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however
could
someone please explain how you avoid using them - is the answer to
have
many
tiny tables with potentially only 2 fields (assuming it is necessary
to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have
several
fields which have 2 or sometimes 3 options and it seems a lot to
create
a
table for this.

Many thanks

Helen

I agree with most of everything in this discussion, however.....I have fields
in my master table that in the 'Data/Type' I used Lookup Wizzard and used a
Table for my data to be looked up. This works perfectly. If the original
data changes, I make the change in my Lookup Table and everything is updated.
Using a Value List for small amounts of data is OK, but if the data contains
very many records the LookUp Table is much easier. I am using the process in
Tables, Queries, Forms based on the query and Reports based on the query.

"BruceM" wrote:

Yes, Insert Lookup Field is the same as using the Lookup Wizard. The
point that the Access MVPs and others have been making is that using a
lookup FIELD in a table can lead to problems. For one thing, it is
difficult if not impossible to change a lookup field to a conventional
field.
Let's say you have a table (tblStatus) for creating status reports, with
information about sales, inventory, etc. If you have a table field for
Department, just make it a text field in the table. On the form, create a
combo box that has Department as its record source. You have two options
now. You can select Value List as the Row Source Type, then in Row Source
type in a list of departments separated by semi-colons. Or you can create a
department table (tblDept), then create a query (qryDept) based on that
table. Using the query will let you specify a sort order. In combo box
properties, select Table/Query as the Row Source Type, then select qryDept
as the Row Source. If tblDept has a PK and a department name, and qryDept
has the same (in that order), you would set your column count to 2, the
column widths to 0";1", and the bound column to 1 if you want to view the
department name and store the PK. If you want to store the department name
itself you would set the bound column to 2. You could also just leave PK
out of qryDept. When you create a combo box its row source and column count
default to 1, and the bound column defaults to 1.
Note that I am describing the use of tools at the user interface level (the
form) to facilitate data entry. This is the standard and time-tested
approach. People sometimes disagree about details, but not about the
concept. In this example, tblDept functions as a lookup table that supplies
values for the Department field in tblStatus. There is no need for user
interface tools at the table level, since users should not be entering or
changing data except through forms. If you create your lookups at the form
level (using combo boxes as I have described, or list boxes), lookup fields
will never be an issue.
In case you have not seen this article, it provides a succinct summary of
the problems with lookup FIELDS in tables.
http://www.mvps.org/access/lookupfields.htm

"HelenJ" wrote in message
...
Thanks for your comprehensive answer Bruce. I think I will stick with PKs
since as you say if you change a value then it will change everywhere -
which
is what I want in this case.

As for the lookups - I must confess that I am still a bit confused - if
you
use insert Lookup field - this seems to be the same as using the lookup
wizard and if you choose to type in "the values that I want" then this
creates a value list. Or is it that at this stage you choose to use more
than one column? Not something that I have ever done either!

I will carry on in happy designing mode :-)

Many thanks
"BruceM" wrote:

I don't think there is really a downside to adding a primary key (PK)
field.
If your table is a list of 50 states in the U.S.A., and if the state name
is
all you are storing, then it probably doesn't matter if you have a PK,
but
it won't hurt either, and you may find you need it later. I'll just add
here that my opinion is that if you are storing a single field it may as
well be the actual value (e.g. the state name) as a PK in a related
table.
However, if you are storing the state name and an abbreviation (and the
state flower or whatever), then you should probably store the PK, and
just
display the other fields without storing them.

There is no single answer to your question about whether to have a PK.
We
have a situation where I work in which the person's name has to appear as
it
was when a particular type of document was created, so I have to store
the
actual name. If I stored the PK, then when a person's name changes the
old
records will show the new name, which is not acceptable. If on the other
hand you are keeping training records, you probably want old training
records to show the person's current name. In that case the PK must be
stored. For another example, consider payroll. A person whose name
changes
receives a W-2 form for the new name, even if some of the paychecks were
made out to the former name. I'm not trying to win a point, but rather
to
suggest you consider the effect on old data of a change to something like
a
person's name (or a country name, for that matter), which is subject to
possible change.

A value list IS NOT the same as a lookup field. A value list may be
something like Yes, No, Maybe. You create it by selecting Value List as
the
Row Source Type (in the combo box properties), and by adding Yes;No;Maybe
to
the Row Source. This is OK for a limited list. If you need to add
"Probably" to the list you can insert that pretty easily into the row
source. However, if it is a list that is subject to change it is often
easier to use a table or query as the row source.

A lookup field is created in table design view by selecting Insert
Lookup
Field. This is the thing that experienced developers urge you to avoid.
The reasons have been discussed in numerous posts and articles. I have
never used a lookup field, so have not experienced first-hand the
problems
that can result.


"HelenJ" wrote in message
...
I (think!) I am clear what a look up table is - but is it a good idea to
have
a primary key in the lookup table? Or could it just be a single field
with
the list of items required to populate the drop down?

I have in the past found that when I have used a value list on a field
in
a
table (I assume this is what you mean by a lookup field) if you then
need
to
alter the values (yes OK you start to win your point here!) the there
is
no
point in altering them in the table because the form doesn't update.

Thanks again

Helen

"BruceM" wrote:

To expand just a bit on what Jeff wrote, if you have a field into
which
you
can put one of just a few words or phrases then you can bind a combo
box
to
that field, and make the combo box row source a Value List. If you
have
a
longer list (states or departments or whatever), and especially if you
have
a list that could one day change, then you may do better to make a
table
containing just those values, and to use that table as the combo box
row
source. This is an entirely different matter than using a lookup
field
in a
table. Lookup tables are fine. There is a big difference between a
lookup
field and a lookup table that is used for populating a field.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however
could
someone please explain how you avoid using them - is the answer to
have
many
tiny tables with potentially only 2 fields (assuming it is necessary
to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have
several
fields which have 2 or sometimes 3 options and it seems a lot to
create
a
table for this.

Many thanks

Helen

Thanks Bruce - very comprehensive!

"BruceM" wrote:

Yes, Insert Lookup Field is the same as using the Lookup Wizard. The
point that the Access MVPs and others have been making is that using a
lookup FIELD in a table can lead to problems. For one thing, it is
difficult if not impossible to change a lookup field to a conventional
field.
Let's say you have a table (tblStatus) for creating status reports, with
information about sales, inventory, etc. If you have a table field for
Department, just make it a text field in the table. On the form, create a
combo box that has Department as its record source. You have two options
now. You can select Value List as the Row Source Type, then in Row Source
type in a list of departments separated by semi-colons. Or you can create a
department table (tblDept), then create a query (qryDept) based on that
table. Using the query will let you specify a sort order. In combo box
properties, select Table/Query as the Row Source Type, then select qryDept
as the Row Source. If tblDept has a PK and a department name, and qryDept
has the same (in that order), you would set your column count to 2, the
column widths to 0";1", and the bound column to 1 if you want to view the
department name and store the PK. If you want to store the department name
itself you would set the bound column to 2. You could also just leave PK
out of qryDept. When you create a combo box its row source and column count
default to 1, and the bound column defaults to 1.
Note that I am describing the use of tools at the user interface level (the
form) to facilitate data entry. This is the standard and time-tested
approach. People sometimes disagree about details, but not about the
concept. In this example, tblDept functions as a lookup table that supplies
values for the Department field in tblStatus. There is no need for user
interface tools at the table level, since users should not be entering or
changing data except through forms. If you create your lookups at the form
level (using combo boxes as I have described, or list boxes), lookup fields
will never be an issue.
In case you have not seen this article, it provides a succinct summary of
the problems with lookup FIELDS in tables.
http://www.mvps.org/access/lookupfields.htm

"HelenJ" wrote in message
...
Thanks for your comprehensive answer Bruce. I think I will stick with PKs
since as you say if you change a value then it will change everywhere -
which
is what I want in this case.

As for the lookups - I must confess that I am still a bit confused - if
you
use insert Lookup field - this seems to be the same as using the lookup
wizard and if you choose to type in "the values that I want" then this
creates a value list. Or is it that at this stage you choose to use more
than one column? Not something that I have ever done either!

I will carry on in happy designing mode :-)

Many thanks
"BruceM" wrote:

I don't think there is really a downside to adding a primary key (PK)
field.
If your table is a list of 50 states in the U.S.A., and if the state name
is
all you are storing, then it probably doesn't matter if you have a PK,
but
it won't hurt either, and you may find you need it later. I'll just add
here that my opinion is that if you are storing a single field it may as
well be the actual value (e.g. the state name) as a PK in a related
table.
However, if you are storing the state name and an abbreviation (and the
state flower or whatever), then you should probably store the PK, and
just
display the other fields without storing them.

There is no single answer to your question about whether to have a PK.
We
have a situation where I work in which the person's name has to appear as
it
was when a particular type of document was created, so I have to store
the
actual name. If I stored the PK, then when a person's name changes the
old
records will show the new name, which is not acceptable. If on the other
hand you are keeping training records, you probably want old training
records to show the person's current name. In that case the PK must be
stored. For another example, consider payroll. A person whose name
changes
receives a W-2 form for the new name, even if some of the paychecks were
made out to the former name. I'm not trying to win a point, but rather
to
suggest you consider the effect on old data of a change to something like
a
person's name (or a country name, for that matter), which is subject to
possible change.

A value list IS NOT the same as a lookup field. A value list may be
something like Yes, No, Maybe. You create it by selecting Value List as
the
Row Source Type (in the combo box properties), and by adding Yes;No;Maybe
to
the Row Source. This is OK for a limited list. If you need to add
"Probably" to the list you can insert that pretty easily into the row
source. However, if it is a list that is subject to change it is often
easier to use a table or query as the row source.

A lookup field is created in table design view by selecting Insert
Lookup
Field. This is the thing that experienced developers urge you to avoid.
The reasons have been discussed in numerous posts and articles. I have
never used a lookup field, so have not experienced first-hand the
problems
that can result.


"HelenJ" wrote in message
...
I (think!) I am clear what a look up table is - but is it a good idea to
have
a primary key in the lookup table? Or could it just be a single field
with
the list of items required to populate the drop down?

I have in the past found that when I have used a value list on a field
in
a
table (I assume this is what you mean by a lookup field) if you then
need
to
alter the values (yes OK you start to win your point here!) the there
is
no
point in altering them in the table because the form doesn't update.

Thanks again

Helen

"BruceM" wrote:

To expand just a bit on what Jeff wrote, if you have a field into
which
you
can put one of just a few words or phrases then you can bind a combo
box
to
that field, and make the combo box row source a Value List. If you
have
a
longer list (states or departments or whatever), and especially if you
have
a list that could one day change, then you may do better to make a
table
containing just those values, and to use that table as the combo box
row
source. This is an entirely different matter than using a lookup
field
in a
table. Lookup tables are fine. There is a big difference between a
lookup
field and a lookup table that is used for populating a field.

"HelenJ" wrote in message
...
I have been reading all about the evils of lookups in tables, however
could
someone please explain how you avoid using them - is the answer to
have
many
tiny tables with potentially only 2 fields (assuming it is necessary
to
have
a PK in every table) and often only 2 records.

I am just starting the design of a new database and I seem to have
several
fields which have 2 or sometimes 3 options and it seems a lot to
create
a
table for this.

Many thanks

Helen

On Thu, 23 Mar 2006 08:06:38 -0800, HelenJ
wrote:

Thanks for your comprehensive answer Bruce. I think I will stick with PKs
since as you say if you change a value then it will change everywhere - which
is what I want in this case.

Just to clarify... don't make the common mistake of equating a Primary
Key with an Autonumber. Access seems to make you want to think they
are the same thing but they are NOT.

For instance, a table of States could and should have the two-letter
state code as its Primary Key, and that is the value you could store
in your Address table. You might want to have the full state name (AK
= Alaska say) in the States table as well. However, if you have a
table of (say) ProjectStatus, it could have just one field, the status
itself ("Proposed", "Approved", "Initiated", ...) as its Primary Key.
If you do so just set the Relationships between the status table and
the other tables using the status to Cascade Updates, so that if you
need to change "Initiated" to "In Progress" the change will apply
everywhere the field is used.

As for the lookups - I must confess that I am still a bit confused - if you
use insert Lookup field - this seems to be the same as using the lookup
wizard and if you choose to type in "the values that I want" then this
creates a value list. Or is it that at this stage you choose to use more
than one column? Not something that I have ever done either!

Just don't use the lookup wizard AT ALL. On the Form toolbar, there is
a Combo Box wizard. This gives you the same choices - type in a value
list, or select an (already existing) lookup table.

John W. Vinson[MVP]