Dependant's Table

Mr. Vinson,

I can't thank you enough for your continued assistance. Your instruction
has been very beneficial.

I made tables for Grade, Rank, Religious Preference, Marital Status, and
Professional affiliation...none of which have a repeating value, so in
essence, wouldn't need an autonumber ID?

Instead of creating tables for these values should I just type them in when
prompted by placing a combobox on a form and having them stored in an
appropriate field? This would certainly limit the number of tables in my
database, not sure how that affects space.

This method will allow me to use queries based off of the actual value (e.g.
M for male) correct?

"John W. Vinson" wrote:

On Sun, 3 Jun 2007 18:28:02 -0700, franklinbukoski
wrote:

I created the Gender Text Field as indicated in the Personnel Table. On the
Form I used a combobox and typed the values: M,F into it (vice looking the
values up in a Gender Table) and stored it in the gender field of the
Personnel table. Worked exactly as you stated, thank you so much.

I also tried creating a table: GenderID (Autonumber), Gender (Text) with the
values M, F, and linking this Gender Text field to the Gender Text field in
Personnel table. On the form, I input a combobox, looked the values up in
the Gender table, selected to store the value in the Personnel table Gender
text field with the validation rule IN ("M","F") and this did not work. The
field would not accept either the M,F choice of the combobox. The
relationships window would not allow me to use referential integrity or
cascade options when I did this.

Correct. It won't.

Your personnel table - with this second option - will contain, not a gender
letter, but a *long integer number* (the GenderID, a link to the numeric
autonumber value). The validation rule will fail because the "lookup field"
does NOT contain what it appears to contain. What *you* see is F or M; what
the table actually contains is 1 or 2. The numeric value 1 does not fit the
validtion rule since it is not equal to F nor is it equal to M.

Access really, really pushes you to give every table a Primary Key - which is
well and good; every table should have one. But it also pushes you to give
every table an Autonumber Primary Key - which is often useful but is
emphatically NOT required.

A Primary Key should meet three tests: it must be unique within its table; it
should be stable, so you don't have to deal with cascading updates if it
changes; and - preferably, but less critically - it should be short. In a
table of Genders, a single character text field meets all three criteria
admirably: it's unique (there won't be two records in the Gender table with
the same value); it's stable (F will always be Female, at least in the
ordinary use of a database; if you translate the database to German it might
become W but that's a one-time operation); and it's a quarter the size of a
Long Integer.

Keep it simple! For gender you don't NEED a Gender table. This is the kind of
data for which list-of-values is ideal.

Expanding to a bit more complex issue, consider a table of US States (and you
can throw in Canadian provinces too). Access may want to give you an
autonumber StateID, a state code ("ID", "MD", "TX") and a state name (Idaho,
Maryland, Texas). I'd argue that the autonumber is redundant and unnecessary.
Instead you can use a two-character text State field in your addresses, and
have the two-character code as the Primary Key of your States table. A combo
box on your form could store the two-letter ID while displaying the state
name, if you wish.

John W. Vinson [MVP]

Good evening,

ISO 5218 is a bit advanced for our business operation. I don't think I
could convince the Boss to let me use 1 for male or 2 for female on our
reports, although he may be glad that Male=1.

I have to look up immutable. Just when I thought I was starting to
understand a couple things...

"Jamie Collins" wrote:

On Jun 4, 5:01 am, John W. Vinson
wrote:
A Primary Key ...
should be stable, so you don't have to deal with cascading updates if it
changes;

Stable immutable

a
table of Genders,

Gender = masculine, feminine, etc.
Sex = male, female, etc.

a single character text field...
[is] stable (F will always be Female, at least in the
ordinary use of a database; if you translate the database to German it might
become W but that's a one-time operation)

"ISO 5218 defines a representation of human sexes through a language-
neutral single-digit code"

http://en.wikipedia.org/wiki/ISO_5218

Jamie.

--

On Mon, 4 Jun 2007 15:27:00 -0700, franklinbukoski
wrote:

Mr. Vinson,

I can't thank you enough for your continued assistance. Your instruction
has been very beneficial.

I made tables for Grade, Rank, Religious Preference, Marital Status, and
Professional affiliation...none of which have a repeating value, so in
essence, wouldn't need an autonumber ID?

That's a judgement call. Some people routinely use autonumbers for all tables;
some people avoid them altogether, or use them only as a last resort; others
(like me) fall in between.

Instead of creating tables for these values should I just type them in when
prompted by placing a combobox on a form and having them stored in an
appropriate field? This would certainly limit the number of tables in my
database, not sure how that affects space.

A Combo Box is going to be used in any case, table or not, just to let your
users pick from a list of valid values. Whether that list is stored in a Table
or in a Value List is up to you. The advantage of a Table is that you can have
just *one* table and use it on many forms in many combo boxes, and it's easier
to maintain; the advantage of a value list is that (for short lists of simple
values) it reduces the number of tables and queries in your database.

This method will allow me to use queries based off of the actual value (e.g.
M for male) correct?

Absolutely. That's one of the real advantages of the "natural key" approach of
storing the human-meaningful text value - it's right there in your table and
you don't need the extra step of a query looking it up.

John W. Vinson [MVP]

On Jun 4, 11:31 pm, franklinbukoski
wrote:
Stable immutable

I have to look up immutable.

Context is important. The use of 'stable key' in the SQL literature
means one that will remain the same for a long time rather than one
that will never change.

An immutable key is fine if you can get it but reality doesn't always
cooperate. Consider that if keys weren't supposed to change in the SQL
world there would be no need for CASCADE referential actions!

ISO 5218 is a bit advanced for our business operation

I have to look up 'advanced' ;-)

I don't think I
could convince the Boss to let me use 1 for male or 2 for female on our
reports...

I suggest you format your data in reports rather than always using raw
data values. Is, for example, your temporal data stored as fully-
formatted text t to target reports or strongly-typed as DATETIME to
favour querying and formatted only when displayed?

I take the point, though, that using 'M' and 'F' makes the data more
human-readable. (incidentally, that is an argument used against
meaningless so-called surrogate keys such as autonumber). ISO 5218 is
particularly advantageous when you would otherwise encode the other
cases yourself: 0 = unknown (rather than NULL = missing), 9 = non-
human legal person (corporate body, governmental organisation, etc).

...although he may be glad that Male=1

Did you read the Wikipedia entry? "The standard explicitly states that
no significance is to be placed on the fact that male is encoded as 1
and female as 2. The encoding merely reflects existing practice in the
countries that initiated this standard."

Better than a YESNO column named IsMale g.

Jamie.

--

I continue to learn more and more. Thanks for the further clarification. I
will research formatting in reports and the like. A while back someone told
me to use Access because it was a very simple database/platform to use as an
introduction into relational databases...hasn't been all that simple for me.

"Jamie Collins" wrote:

On Jun 4, 11:31 pm, franklinbukoski
wrote:
Stable immutable

I have to look up immutable.

Context is important. The use of 'stable key' in the SQL literature
means one that will remain the same for a long time rather than one
that will never change.

An immutable key is fine if you can get it but reality doesn't always
cooperate. Consider that if keys weren't supposed to change in the SQL
world there would be no need for CASCADE referential actions!

ISO 5218 is a bit advanced for our business operation

I have to look up 'advanced' ;-)

I don't think I
could convince the Boss to let me use 1 for male or 2 for female on our
reports...

I suggest you format your data in reports rather than always using raw
data values. Is, for example, your temporal data stored as fully-
formatted text t to target reports or strongly-typed as DATETIME to
favour querying and formatted only when displayed?

I take the point, though, that using 'M' and 'F' makes the data more
human-readable. (incidentally, that is an argument used against
meaningless so-called surrogate keys such as autonumber). ISO 5218 is
particularly advantageous when you would otherwise encode the other
cases yourself: 0 = unknown (rather than NULL = missing), 9 = non-
human legal person (corporate body, governmental organisation, etc).

...although he may be glad that Male=1

Did you read the Wikipedia entry? "The standard explicitly states that
no significance is to be placed on the fact that male is encoded as 1
and female as 2. The encoding merely reflects existing practice in the
countries that initiated this standard."

Better than a YESNO column named IsMale g.

Jamie.

--

How then do I get around using a child table that stores a "number" in the
parent table for use in queries and such. Do I just have to know (we'll use
gender for simplicity purposes) that 1=male, 2=female, and use those numbers
as criteria?

As the choices increase I won't be able to remember their numeric value, and
the database is going to be highly query/report driven.

"John W. Vinson" wrote:

On Mon, 4 Jun 2007 15:27:00 -0700, franklinbukoski
wrote:

Mr. Vinson,

I can't thank you enough for your continued assistance. Your instruction
has been very beneficial.

I made tables for Grade, Rank, Religious Preference, Marital Status, and
Professional affiliation...none of which have a repeating value, so in
essence, wouldn't need an autonumber ID?

That's a judgement call. Some people routinely use autonumbers for all tables;
some people avoid them altogether, or use them only as a last resort; others
(like me) fall in between.

Instead of creating tables for these values should I just type them in when
prompted by placing a combobox on a form and having them stored in an
appropriate field? This would certainly limit the number of tables in my
database, not sure how that affects space.

A Combo Box is going to be used in any case, table or not, just to let your
users pick from a list of valid values. Whether that list is stored in a Table
or in a Value List is up to you. The advantage of a Table is that you can have
just *one* table and use it on many forms in many combo boxes, and it's easier
to maintain; the advantage of a value list is that (for short lists of simple
values) it reduces the number of tables and queries in your database.

This method will allow me to use queries based off of the actual value (e.g.
M for male) correct?

Absolutely. That's one of the real advantages of the "natural key" approach of
storing the human-meaningful text value - it's right there in your table and
you don't need the extra step of a query looking it up.

John W. Vinson [MVP]

On Tue, 5 Jun 2007 04:00:01 -0700, franklinbukoski
wrote:

How then do I get around using a child table that stores a "number" in the
parent table for use in queries and such. Do I just have to know (we'll use
gender for simplicity purposes) that 1=male, 2=female, and use those numbers
as criteria?

As the choices increase I won't be able to remember their numeric value, and
the database is going to be highly query/report driven.

Neither you nor your users should even SEE the autonumber values, much less
need to remember them!!! They're "under the hood", used internally for linking
things together; they should not be exposed to user view.

If you're creating queries by entering literal search values on the Criteria
line in the query grid, it's time to move on to the much more powerful
Parameter Query. You can create a Form with an unbound combo box (cboSex let's
say) and use

=[Forms]![NameOfYourForm]![cboSex]

as a criterion on the query.

That said - let me reiterate that for a field like Sex I would always use a
list of values and actually store the text. I see Jamie's point and respect
it, but for the applications that I have built it's overkill. Again, you need
to evaluate each case on its own requirements.

John W. Vinson [MVP]