Dependant's Table

[Guest]

I have a dependant's table with the fields for spouse, children, and
additional (if you care for your parent's or other).

Should these three categories have their own table?

When I view the data in the table, one record may have multiple rows, for
example, one spouse, five children, and 1 other dependant will have 6 rows of
data, but there is mostly empty fields as only one of the six rows contains
spouse data.

Also, once I design a form to input this data I can not input a second
"additional" dpendant without creating an empty second child field.

 

[John W. Vinson]

I have a dependant's table with the fields for spouse, children, and
additional (if you care for your parent's or other).

Should these three categories have their own table?

"Fields are expensive, records are cheap". Having separate *fields* for
spice... umm sorry, the spouse... and for children is not the best design.

I would suggest a table of Dependents, with a primary key DependentID; a
foreign key to your (employee?? whatever it is) main table; a field for the
type of relationship (spouse, parent, child, grandchild, domestic partner,
....); and fields for biographical data (lastname, firstname, date of birth,
etc.).


John W. Vinson [MVP]

 

[Guest]

Mr. Vinson,

Thank you very much.

Once I create the Relationship Field, is it better to have the relationships
in a separate table and use a lookup wizard to identify it, or link it to the
separate table by Relationship ID, or use the lookup wizard for the
Relationship field itself to type in the values I want?

 

[John W. Vinson]

Mr. Vinson,

Thank you very much.

Once I create the Relationship Field, is it better to have the relationships
in a separate table and use a lookup wizard to identify it, or link it to the
separate table by Relationship ID, or use the lookup wizard for the
Relationship field itself to type in the values I want?

I'd recommend never using the Lookup Wizard at all, in any circumstances. See
http://www.mvps.org/access/lookupfields.htm for a critique of this misfeature.

Instead, by all means use lookups - create a Relationships table with the list
of valid relationships, and use a Combo Box based on this table on the Form
which you use to enter and edit your data.

If you're entering or editing data in table datasheets... well, don't. Use a
form; they're much more flexible, controllable, programmable and usable than
tables.

John W. Vinson [MVP]

 

[Guest]

Sir,

I tested this out on another field, Gender.

Originally, I had a main table "Personnel Data" with a GenderID field that
used the lookup wizard to choose between male and female, from a "Gender"
table. I then deleted the relationship, deleted the GenderID field from the
"Personnel Data" table, deleted that field from my main form, added a foreign
key to "Gender" table of PersonnelID, created a relationship from "Personnel
Data" PersonnelID to "Gender" PersonnelID, then inserted a combobox on my
main form from table "Gender" as SELECT tblGender.GenderID, tblGender.Gender
FROM tblGender;

When I select a Gender now, it does not save in the record. What step am I
missing?

 

[John W. Vinson]

Sir,

I tested this out on another field, Gender.

Originally, I had a main table "Personnel Data" with a GenderID field that
used the lookup wizard to choose between male and female, from a "Gender"
table. I then deleted the relationship, deleted the GenderID field from the
"Personnel Data" table, deleted that field from my main form, added a foreign
key to "Gender" table of PersonnelID, created a relationship from "Personnel
Data" PersonnelID to "Gender" PersonnelID, then inserted a combobox on my
main form from table "Gender" as SELECT tblGender.GenderID, tblGender.Gender
FROM tblGender;

When I select a Gender now, it does not save in the record. What step am I
missing?

That's BACKWARD!

Each Person has one and only one gender. Each gender can apply to many
Persons. The foreign key goes in the "many" side table - the Personnel Data
table to wit.

Your Personnel table should have a Gender field; I'd say Text, with validtion
rule

IN ("M","F")

and use a combo box with a list of values "M","F" to populate it.

You're making it MUCH harder than it needs to be.

John W. Vinson [MVP]

 

[Guest]

Thank you once again, I'm determined to figure this out and your instruction
is very helpful!

 

[Guest]

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.

 

[John W. Vinson]

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]

 

[Jamie Collins]

A Primary Key ...
should be stable, so you don't have to deal with cascading updates if it
changes;

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.

--

 

[Guest]

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?

 

[Guest]

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...

A Primary Key ...
should be stable, so you don't have to deal with cascading updates if it
changes;

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.

 

[John W. Vinson]

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]

 

[Jamie Collins]

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.

--

 

[Guest]

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.

 

[Guest]

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]

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]