I am debating

  • Thread starter Thread starter McDonald
  • Start date Start date
M

McDonald

When doing titles, Mr. Mrs. Miss. Should I create a table that contains
them and create a relationship to that field in another table. Or should I
just create them within a drop down box of values? In any case I want to be
able to select them from a dropdown list in a form rather then type them in
each time. however, their might be an instance in the future where I need
to add to that list and can I do that by just typing it into the combo box
and have it available in that list from that time on.
 
I would decide how much editing you would need to do in
the future. If there isn't very much, I would just put
those values in the drop down box. With titles like that,
I can't magine much editing in the future. However, if it
is something that will change quite often, a table would
be the way to go.
 
I won't be editing that much, so I guess I will put them in as values, how
do I do that. Should I do it in a table where they will be stored or in the
actual form itself?
 
McDonald,

If you want to select these for data entry from a combobox, then the
combobx's RowSource property is the place to be. If you set the Row
Source Type property to Value List, then you can type the items
straight into the Row Source property, such as...
"Mr";"Mrs";"Capn" etc
If you make a table to store these (this would always be my choice!),
then the Row Source Type property would be set to Table/Query and you
enter the name of the table in for the Row Source. If you do it with
a table, I see no reason to define a Relationship to any existing
tables.

- Steve Schapel, Microsoft Access MVP
 
McDonald said:
When doing titles, Mr. Mrs. Miss. Should I create a table that contains
them and create a relationship to that field in another table. Or should I
just create them within a drop down box of values? In any case I want to be
able to select them from a dropdown list in a form rather then type them in
each time. however, their might be an instance in the future where I need
to add to that list and can I do that by just typing it into the combo box
and have it available in that list from that time on.
Click to expand...

There might be a lot more of these titles than you may
think: Dr, Rev, etc. along with military ranks and others.

I definitly would put it in a table, probably with another
field to use to sort the titles in an order that's
independent of their spelling. You should also seriously
consider whether to use a surrogate primary key (autonumber)
or the natural key. If you use a surrogate key, make sure
the title field is also maked as a unique index. If you use
the natural key, make sure you set up enforced referential
integrity with Cascade Updates (but not Cascade Delete).

As for occasionally changing/adding to the list, I would not
do this through the NotInList event, It's just too easy to
create a plethora of different spellings for the same thing.
Instead, create a small form for an adminstrator type person
to add/edit rows in the table.
 
Marshall Barton wrote:
"You should also seriously
consider whether to use a surrogate primary key (autonumber)
or the natural key. If you use a surrogate key, make sure
the title field is also maked as a unique index. If you use
the natural key, make sure you set up enforced referential
integrity with Cascade Updates (but not Cascade Delete)."

I am not quite clear as to what you mean here, could you give me an
explination? I have it set up with id (Autonumber) a title field and a full
field i.e. Dr. and Doctor.
 
McDonald said:
Marshall Barton wrote:
"You should also seriously
consider whether to use a surrogate primary key (autonumber)
or the natural key. If you use a surrogate key, make sure
the title field is also maked as a unique index. If you use
the natural key, make sure you set up enforced referential
integrity with Cascade Updates (but not Cascade Delete)."

I am not quite clear as to what you mean here, could you give me an
explination? I have it set up with id (Autonumber) a title field and a full
field i.e. Dr. and Doctor.
Click to expand...

An autonumber primary key is a surrogate for the real data
that identifies the record. To prevent duplicate entries,
you should make a Unique Index for the title field. In this
case it's probably not a huge issue, just a safety first
cautiousness.

As I said before, think about adding another field that you
can use to sort the titles. You may (or may not) want to
clump the common ones (Mr, Miss, etc) at the top of the list
and the military one at the end of the list. It might make
it easier for your users to navigate the list if they don't
have to scroll way down the list (past Cpl, Gen, Lt, Lt Col,
Maj, etc) to get to Mrs.
--
Marsh
MVP [MS Access]


 
Ok, I have the primary key as an autonumber and the title field as an index
yes no duplicates. (I think that is what you meant) please correct me if I
am wrong. how do I clump the common ones together? also how do I go about
linking the two tables afterwords, do I have to create a relationship
between titleid's in each table or shoul I select titles under lookup combo
box and set it's row source?
Marshall Barton said:
McDonald said:
Marshall Barton wrote:
"You should also seriously
consider whether to use a surrogate primary key (autonumber)
or the natural key. If you use a surrogate key, make sure
the title field is also maked as a unique index. If you use
the natural key, make sure you set up enforced referential
integrity with Cascade Updates (but not Cascade Delete)."

I am not quite clear as to what you mean here, could you give me an
explination? I have it set up with id (Autonumber) a title field and a full
field i.e. Dr. and Doctor.
Click to expand...

An autonumber primary key is a surrogate for the real data
that identifies the record. To prevent duplicate entries,
you should make a Unique Index for the title field. In this
case it's probably not a huge issue, just a safety first
cautiousness.

As I said before, think about adding another field that you
can use to sort the titles. You may (or may not) want to
clump the common ones (Mr, Miss, etc) at the top of the list
and the military one at the end of the list. It might make
it easier for your users to navigate the list if they don't
have to scroll way down the list (past Cpl, Gen, Lt, Lt Col,
Maj, etc) to get to Mrs.
--
Marsh
MVP [MS Access]


should
I to
be them
in
Click to expand...
Click to expand...
 
McDonald said:
Ok, I have the primary key as an autonumber and the title field as an index
yes no duplicates. (I think that is what you meant) please correct me if I
am wrong.
Click to expand...

You got it.

How do I clump the common ones together?
Click to expand...

Add another field (call it SortOrder) to the table, data
type single or double. Then you can assign values to this
field to sort the records in any desired way. E.g. Mr - 1,
Mrs - 2, Ms - 2.1, Miss - 2.3, Dr - 4, . . . Cpl - 100.01,
Sgt - 100.02, Lt - 200.01, Cptn - 200.02, Maj - 200.4, Lt
Col - 200.6, Col - 200.61, Gen 200.8

The RowSource for the combo box could be:
SELECT titleid, title FROM titles ORDER BY SortOrder
and its BoundColumn is 1, ColumnWidths are 0;1

Also how do I go about
linking the two tables afterwords, do I have to create a relationship
between titleid's in each table or shoul I select titles under lookup combo
box and set it's row source?
Click to expand...

I assumed the combo box was the way you wanted users to
select a title.

As for relationships, I recommend that you stick with the
usual relational integrity mechanism and create an enforced
relationship between any tables that store the titleID and
the titles table.
--
Marsh
MVP [MS Access]


 
Back
Top