If i need a dropdown field (e.g. A, B or C) - is it best to design it in the table or the form?

Dave,

Thanks for the post. I think that what you say is right 98% of the time.
Here's a real world example example of the 2%

Access (and these posts) is my hobby, for the last 28 years my day job has
been running small (20-50 person) very diverse technical companies.
(currently fsinet.com) ANy application is a learnig curve investment, and,
for my folks creating information storage systems (and being diverse, we have
lots of them) I have them very Access oriented (vs. Excell) and we use it for
nearly all information storage. Just short of grocery lists, all
structured/list/tabular data goes into Access. I have a lot of folks who
are well-disciplined enough to enter data through queries. And also the need
to do a lots of creative things with queries, in a typical day under our
roof, 30 query "redesigns" might get done, eg. taking a few seconds to set
some never-before-imagined multi-field criteria, create some multi-field
sorts. And many of these fields are populated by selecting from dropdown
lists, usually designed into the table where another single-field table. And
we've never ever had a problem with these. A lot of these are people that
I bill out at $125 - $200 per hour. In short, the current process needs
dropdown lists in queries. My alternative would be the expense of
shift man-hours of theirs over to designing 30 new forms per day in order to
"solve" problems that don't exist here.


Maybe there is that 2%!


Fred






"Klatuu" wrote:

Fred,
I respect your opinions are your posts here are always very helpful.
In this case, however, we will have to agree to disagree. You would not be
able to convence me a lookup field is a good alternative in any situation.
First, it is confusing to users. How many posts have you seen here where
the OP can't figure out why he is getting numbers instead of what he thought
was in the field?

And, an important consideration is that should upsizing become a serious
consideration, it is a redo. Only Jet has lookup fields. To upsize to SQL
Server, you would have to rewrite it as I have described any way.

But, keep up the good work, Fred. I alway enjoy reading your posts.
--
Dave Hargis, Microsoft Access MVP


"Fred" wrote:

Dave,

There is absolutely no basis for saying what you said. The types of
situations where the advantages of an embedded-in-table lookups outweigh the
disadvantages (with a lengthier post I could describe some examples) have
absolutely nothing do with the attributes that differentiate between
spreadsheet and database structures/applications.

Fred






"Klatuu" wrote:

"Us little people often work directly in queries and sometimes even directly
in tables."

Then you don't really have an application. It is most likely a "spreadsheet
on steriods"

IMHO, data belongs in tables. A list of options is data.

--
Dave Hargis, Microsoft Access MVP


"Fred" wrote:

Jethro's question could be either of two questions:

1. Hard code the list (e.g. in a form) vs. using a seperate table for the
source

2. Embedded in the main table design vs. "add on" later (in a form)

Albert answered #1. If it's question #2, the developers say "add on
later" and I usually embed it in the table. The difference is that
developers usually deal with larger groups of users, or uncontrollable users.
And so they are in an a situation where the can and need to limit the user
interface to forms. Us little people often work directly in queries and
sometimes even directly in tables. The "embed in the table" idea has some
downsides, but has the advantage of making the dropdown available in the
queries and tables, not just forms.

"Albert D. Kallal" wrote:

"JethroUK©" wrote in message
...
If i need a dropdown field (e.g. A, B or C) - is it best to design it in
the table or the form?

I think the answer depends on if the list will ever change?

if the drop down is to be only limited to two options, then you can go with
building it into the list. (say, "night" and "day" being the only options.

However, in almost ALL cases it is better to go with a small table. The
*big* reason is two fold:

1 - that silly boss always comes along and changes his mind..and add's more
options. If you use a table, then additional options can be added over time
without having to design/modify the form.

2 - for user friendly software, you have to report on that data, and sure
enough you want a prompt screen that allows the user to select/filter by
those options. If you put the list in the form then when you built another
form that prompts for reports etc, you likely need that list again. If the
list is based on a table then you can use that one master list in any place
in the application.

It usually best to avoid hard coding the list in the form since then you
start to have to maintain duplicate copies of the list.

Since it really is never much a performance issue, the choice is about most
flexibly and that's useally a table.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Just for the record - most of my fields will be using lookup/dropdown for
speed and accuracy of entry for user friendliness - albeit most will be
"text only" fields where it will simply look up any previously used 'text'
(no index field, no separate table) - e.g. 'Town field': will find only
previously used towns, but retain the option to just type in any not in list

Used this method before very effectively

I am inclined to design these into the table from the start - but it's
equally possible just to design these into the form as ness



"Klatuu" wrote in message
...
I would agree you should not use lookup fields, but my post is explaining
the
correct way to handle it.
--
Dave Hargis, Microsoft Access MVP


"bcap" wrote:

I think most people here are answering the wrong question. The way it
reads
to me, the question Jethro is asking is, quite simply, "Should I create a
lookup field in a table?".

To which the answer is emphatically "NO"!

http://www.mvps.org:80/access/lookupfields.htm

"JethroUK©" wrote in message
...


"Klatuu" wrote in message
...


IMHO, data belongs in tables. A list of options is data.


so would you include a dropdown in the table design or the form
design? -
i'm inclined to put it in the table but after few into the design i
don't
want to change my mind

JethroUK© wrote:
Just for the record - most of my fields will be using lookup/dropdown
for speed and accuracy of entry for user friendliness - albeit most
will be "text only" fields where it will simply look up any
previously used 'text' (no index field, no separate table) - e.g.
'Town field': will find only previously used towns, but retain the
option to just type in any not in list
Used this method before very effectively

I am inclined to design these into the table from the start - but it's
equally possible just to design these into the form as ness

It should be noted that when you have a drop down list that stores the same
value that is shown in the list that this pretty much eliminates all of the
negatives about using a table lookup field. The problems with table lookup
fields is in making it appear that the table contains one value when it
actually contains another.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

You situation is atypical and I would agree that with knowledgible personnel,
the way you are using Access is a very good use. If lookup fields work for
you, fine, but for a typical application build for non technical users, I
would not use them.
--
Dave Hargis, Microsoft Access MVP


"Fred" wrote:

Dave,

Thanks for the post. I think that what you say is right 98% of the time.
Here's a real world example example of the 2%

Access (and these posts) is my hobby, for the last 28 years my day job has
been running small (20-50 person) very diverse technical companies.
(currently fsinet.com) ANy application is a learnig curve investment, and,
for my folks creating information storage systems (and being diverse, we have
lots of them) I have them very Access oriented (vs. Excell) and we use it for
nearly all information storage. Just short of grocery lists, all
structured/list/tabular data goes into Access. I have a lot of folks who
are well-disciplined enough to enter data through queries. And also the need
to do a lots of creative things with queries, in a typical day under our
roof, 30 query "redesigns" might get done, eg. taking a few seconds to set
some never-before-imagined multi-field criteria, create some multi-field
sorts. And many of these fields are populated by selecting from dropdown
lists, usually designed into the table where another single-field table. And
we've never ever had a problem with these. A lot of these are people that
I bill out at $125 - $200 per hour. In short, the current process needs
dropdown lists in queries. My alternative would be the expense of
shift man-hours of theirs over to designing 30 new forms per day in order to
"solve" problems that don't exist here.


Maybe there is that 2%!


Fred






"Klatuu" wrote:

Fred,
I respect your opinions are your posts here are always very helpful.
In this case, however, we will have to agree to disagree. You would not be
able to convence me a lookup field is a good alternative in any situation.
First, it is confusing to users. How many posts have you seen here where
the OP can't figure out why he is getting numbers instead of what he thought
was in the field?

And, an important consideration is that should upsizing become a serious
consideration, it is a redo. Only Jet has lookup fields. To upsize to SQL
Server, you would have to rewrite it as I have described any way.

But, keep up the good work, Fred. I alway enjoy reading your posts.
--
Dave Hargis, Microsoft Access MVP


"Fred" wrote:

Dave,

There is absolutely no basis for saying what you said. The types of
situations where the advantages of an embedded-in-table lookups outweigh the
disadvantages (with a lengthier post I could describe some examples) have
absolutely nothing do with the attributes that differentiate between
spreadsheet and database structures/applications.

Fred






"Klatuu" wrote:

"Us little people often work directly in queries and sometimes even directly
in tables."

Then you don't really have an application. It is most likely a "spreadsheet
on steriods"

IMHO, data belongs in tables. A list of options is data.

--
Dave Hargis, Microsoft Access MVP


"Fred" wrote:

Jethro's question could be either of two questions:

1. Hard code the list (e.g. in a form) vs. using a seperate table for the
source

2. Embedded in the main table design vs. "add on" later (in a form)

Albert answered #1. If it's question #2, the developers say "add on
later" and I usually embed it in the table. The difference is that
developers usually deal with larger groups of users, or uncontrollable users.
And so they are in an a situation where the can and need to limit the user
interface to forms. Us little people often work directly in queries and
sometimes even directly in tables. The "embed in the table" idea has some
downsides, but has the advantage of making the dropdown available in the
queries and tables, not just forms.

"Albert D. Kallal" wrote:

"JethroUK©" wrote in message
...
If i need a dropdown field (e.g. A, B or C) - is it best to design it in
the table or the form?

I think the answer depends on if the list will ever change?

if the drop down is to be only limited to two options, then you can go with
building it into the list. (say, "night" and "day" being the only options.

However, in almost ALL cases it is better to go with a small table. The
*big* reason is two fold:

1 - that silly boss always comes along and changes his mind..and add's more
options. If you use a table, then additional options can be added over time
without having to design/modify the form.

2 - for user friendly software, you have to report on that data, and sure
enough you want a prompt screen that allows the user to select/filter by
those options. If you put the list in the form then when you built another
form that prompts for reports etc, you likely need that list again. If the
list is based on a table then you can use that one master list in any place
in the application.

It usually best to avoid hard coding the list in the form since then you
start to have to maintain duplicate copies of the list.

Since it really is never much a performance issue, the choice is about most
flexibly and that's useally a table.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Dave,

I forgot to say that I've learned a lot from your posts. You know Access 5
times better than I do and Access Programming 500 times better than I do.

Sincerely,

Fred



"Klatuu" wrote:

You situation is atypical and I would agree that with knowledgible personnel,
the way you are using Access is a very good use. If lookup fields work for
you, fine, but for a typical application build for non technical users, I
would not use them.
--
Dave Hargis, Microsoft Access MVP

Thanks, Fred.
I checked out your web site. You are involved in technology way over my
head. I have always been a plain old business applications developer. GL,
Inventory, and many different specialties, but nothing in the scientific
realm.
--
Dave Hargis, Microsoft Access MVP


"Fred" wrote:


Dave,

I forgot to say that I've learned a lot from your posts. You know Access 5
times better than I do and Access Programming 500 times better than I do.

Sincerely,

Fred



"Klatuu" wrote:

You situation is atypical and I would agree that with knowledgible personnel,
the way you are using Access is a very good use. If lookup fields work for
you, fine, but for a typical application build for non technical users, I
would not use them.
--
Dave Hargis, Microsoft Access MVP