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

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

"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

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

"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

"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

In your data table you want a Numeric Long Integer field to store a
reference to the code.

Then you want a table with the codes that has two fields.
An Autonumber field that is the primary key of the code table
A text field that contains the description of the code.

You would enter each option as a record. If you have A, B, and C, then you
would have 3 records.

Then on your form, your combo box would have a row source of:
SELECT codeID, CodeDescription FROM tblCode ORDER BY CodeDescription;

Set the following properties:
Bound Column = 1
Colmumn Count = 2
Column Widths = 0";1" = The 0" will make the codeID hidden. The 1" can be
whatever you need to display the descritive text.

The combo should be bound to the Code field in the main data table. Now,
when you select an option, the numeric value of that option will be store in
the main table.

--
Dave Hargis, Microsoft Access MVP


"JethroUK©" wrote:



"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

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

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

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

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