J
JethroUK©
If i need a dropdown field (e.g. A, B or C) - is it best to design it in the
table or the form?
table or the form?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
If i need a dropdown field (e.g. A, B or C) - is it best to design it in the table or the form?
A
Albert D. Kallal
JethroUK© said: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.
F
Fred
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.
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.
K
Klatuu
"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.
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.
J
JethroUK©
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
K
Klatuu
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.
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.
B
bcap
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
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
K
Klatuu
I would agree you should not use lookup fields, but my post is explaining the
correct way to handle it.
correct way to handle it.
F
Fred
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
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
K
Klatuu
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.
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.
F
Fred
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
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
J
JethroUK©
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
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
R
Rick Brandt
JethroUK© said: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.
K
Klatuu
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.
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.
F
Fred
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
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
K
Klatuu
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.
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.