Dependant's Table

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.

On Sun, 3 Jun 2007 07:50:00 -0700, franklinbukoski
wrote:

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]

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" wrote:

On Sun, 3 Jun 2007 07:50:00 -0700, franklinbukoski
wrote:

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]

On Sun, 3 Jun 2007 13:00:01 -0700, franklinbukoski
wrote:

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]

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" wrote:

On Sun, 3 Jun 2007 13:00:01 -0700, franklinbukoski
wrote:

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]

On Sun, 3 Jun 2007 15:52:00 -0700, franklinbukoski
wrote:

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]

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

"John W. Vinson" wrote:

On Sun, 3 Jun 2007 15:52:00 -0700, franklinbukoski
wrote:

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]

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" wrote:

On Sun, 3 Jun 2007 15:52:00 -0700, franklinbukoski
wrote:

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]

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]

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.

--