Zero to Many Relationship

[Jim Shaw]

BlankHow do I define a 0:M relationship between two tables?

I have a reference table: "tblProfessions"
I have a table of interest: "tblJobs" with a foreign key to tblProfessions
(Indexed, dups OK, NULLs allowed)
The Business Rules are:
"All jobs are defined in tblJobs" (about 200 of them now)
"All Professions are defined in tblProfessions" (only 5 professions so
far)
"Jobs may, or may not, have a related profession"
"For any given job, their having, or not having, a profession can change
over time"
"A Profession is a job where practitioners are controlled and licensed
by an outside organization
e.g., Registered Nurses vs Janitor"

In this context, I'm working with a query joining tblJobs & tblProfessions
in my form.
I need an underlying query that returns all jobs with the Profession data if
present (Outer Join)
and allows me to update the profession FK in the current Job record. (e.g.;
Janitors become licensed by the state)

How do you all handle this situation?

Thanks
Jim

 

[Jack MacDonald]

A standard one-to-many relation is all that you need. The fact that
you allow NULL in the tblJob in the foreign key to tblProfession
facilitates the "0" part of your requirement.

If you have the additional requirement that any particular job may
have more than one profession associated with it, then you need to set
up a many-to-many relationship using a linking table.

BlankHow do I define a 0:M relationship between two tables?

I have a reference table: "tblProfessions"
I have a table of interest: "tblJobs" with a foreign key to tblProfessions
(Indexed, dups OK, NULLs allowed)
The Business Rules are:
"All jobs are defined in tblJobs" (about 200 of them now)
"All Professions are defined in tblProfessions" (only 5 professions so
far)
"Jobs may, or may not, have a related profession"
"For any given job, their having, or not having, a profession can change
over time"
"A Profession is a job where practitioners are controlled and licensed
by an outside organization
e.g., Registered Nurses vs Janitor"

In this context, I'm working with a query joining tblJobs & tblProfessions
in my form.
I need an underlying query that returns all jobs with the Profession data if
present (Outer Join)

An Inner Join will return the records for which the Profession data is
present. An Outer Join will return all the records, regardless of the
Profession status.

and allows me to update the profession FK in the current Job record. (e.g.;
Janitors become licensed by the state)

Simply update the Profession field in the tblJobs

How do you all handle this situation?

Thanks
Jim

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[Tim Ferguson]

A standard one-to-many relation is all that you need. The
fact that
you allow NULL in the tblJob in the foreign key to
tblProfession
facilitates the "0" part of your requirement.

The catch is that Access unhelpfully fills in zero as the DefaultValue for
all numeric fields. This is a killer on FK fields, particularly with ones
that reference autonumbers, as that is one value that is never going to be
legal!

Make sure you remove the "0", or replace it with Null, in the table design
view.

HTH


Tim F