Query for Subform Needed Please

[cp2599]

I have two tables Individual and Family with two one to many
relationships between the two (individual can belong to many family).
How do I write a query so that my subform shows all Family records
where the individual is listed as either the applicant or coapplicant.

IndID
IndName

FamilyID
ApplID (foreign key to IndID)
CoApplID (foreign key to IndID)
FamName

Thank you.

 

[cp2599]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Actually, you have a many-to-many relationship:  one individual can
belong to many families; and, one family can have many individuals.
This will require another table to join the Individuals and Families
tables together (to enable the many-to-many relationship).

Individuals
   IndID
   IndName

Families
   FamilyID
   FamName

FamilyIndividuals
   IndId
   FamilyID

The relationships are:

   Individuals (1) -> (M) FamilyIndividuals (M) -> (1) Families

For the sub-form that shows families per individual you'd have a query
like this:

SELECT FI.IndID, FI.FamilyID, F.FamName
FROM FamilyIndividuals AS FI INNER JOIN Families As F ON FI.FamilyID =
F.FamilyID
ORDER BY F.FamName

The FI.IndID would be the Child Link field to the main form's Master
Link field, which would also be the IndID from the Individuals table.
The Individuals table would be the main form's Record Source (or you
could use a query that uses the significant columns from the Individuals
table).

The subform would be a Datasheet with one column:  Families.  The sub
form would not show the FI.IndID.  The FI.FamilyID and the F.FamName
would be in a ComboBox.  The ComboBox would have properties like this:

Column Count: 2
Column Widths: 0",1.5"
Bound Column: 1
Limit to List: Yes
Row Source Type: Table/Query
Row Source:  SELECT FamID, FamName FROM Families ORDER BY FamName

The FamName value would show under the Column "Families."

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSkJaGYechKqOuFEgEQI+vQCg9zztfnQkFYCOloKCHXKWHFD5w30Anjju
TTzz7kYWg4LLcz9oRjHtYZzG
=+ben
-----END PGP SIGNATURE-----

OUCH! No wonder I couldn't get it to work ... nothing like changing
your model midway through the application. Thank you for the quick
and eye-opening response.

 

[cp2599]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Actually, you have a many-to-many relationship:  one individual can
belong to many families; and, one family can have many individuals.
This will require another table to join the Individuals and Families
tables together (to enable the many-to-many relationship).

Individuals
   IndID
   IndName

Families
   FamilyID
   FamName

FamilyIndividuals
   IndId
   FamilyID

The relationships are:

   Individuals (1) -> (M) FamilyIndividuals (M) -> (1) Families

For the sub-form that shows families per individual you'd have a query
like this:

SELECT FI.IndID, FI.FamilyID, F.FamName
FROM FamilyIndividuals AS FI INNER JOIN Families As F ON FI.FamilyID =
F.FamilyID
ORDER BY F.FamName

The FI.IndID would be the Child Link field to the main form's Master
Link field, which would also be the IndID from the Individuals table.
The Individuals table would be the main form's Record Source (or you
could use a query that uses the significant columns from the Individuals
table).

The subform would be a Datasheet with one column:  Families.  The sub
form would not show the FI.IndID.  The FI.FamilyID and the F.FamName
would be in a ComboBox.  The ComboBox would have properties like this:

Column Count: 2
Column Widths: 0",1.5"
Bound Column: 1
Limit to List: Yes
Row Source Type: Table/Query
Row Source:  SELECT FamID, FamName FROM Families ORDER BY FamName

The FamName value would show under the Column "Families."

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSkJaGYechKqOuFEgEQI+vQCg9zztfnQkFYCOloKCHXKWHFD5w30Anjju
TTzz7kYWg4LLcz9oRjHtYZzG
=+ben
-----END PGP SIGNATURE-----

Before adding the individuals to a new family, how would I check to
make sure that the same combination of individuals doesn't already
exist on another family?

 

[cp2599]

cp2599wrote:

Before adding the individuals to a new family, how would I check to
make sure that the same combination of individuals doesn't already
exist on another family?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I thought that's what you wanted your DB to do - "individual can belong
to many family [sic]."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSkQ7JoechKqOuFEgEQIRywCdE1PELfMz8zfiipD+sYq9Z5O/pBwAoLIE
bLJ2lLABF+8YF7XsBOit+THb
=W6uz
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -

Yes that is true - they can belong to many families. For example as
an applicant only or with a coapplicant. I do not want the same two
individuals to belong to two different families.

I got around it by creating a working table that contains
FamID
ApplicantID
CoApplicantID
that I maintain everytime I add/delete or change applicants on a
family.

 

[cp2599]

cp2599wrote:

Before adding the individuals to a new family, how would I check to
make sure that the same combination of individuals doesn't already
exist on another family?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I thought that's what you wanted your DB to do - "individual can belong
to many family [sic]."

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSkQ7JoechKqOuFEgEQIRywCdE1PELfMz8zfiipD+sYq9Z5O/pBwAoLIE
bLJ2lLABF+8YF7XsBOit+THb
=W6uz
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -

I do need an individual to belong to many families for example as an
applicant only or as an applicant with a co-applicant, but the same
two individuals cannot belong to more than one family. I created a
work table and am maintaining it as I add, delete or change
applicants. It's probably not the right way to do this, but it's
working for now. If you know of a book that explains how to work with
multiple queries/subqueries, please let me know its name.

WorkTable:
FamID (key)
ApplID (key)
CoApplID (key)