In Access How do I link contacts (i.e spouses) in the same table

I am trying to create a contact database where both the primary contact and
the spouse can both be clients. They need to be listed as separate contacts
since they can both own products, but I also need to link to each other. I
prefer to do this in the same table but I worry about this creating two
listings for each HUSBAND/WIFE and WIFE/HUSBAND combination.

off the cuff - one way would be to create a new field "CoupleID" and for such
couples supply a unique ID that they share.
--
NTC


"Bill Adkins" wrote:

I am trying to create a contact database where both the primary contact and
the spouse can both be clients. They need to be listed as separate contacts
since they can both own products, but I also need to link to each other. I
prefer to do this in the same table but I worry about this creating two
listings for each HUSBAND/WIFE and WIFE/HUSBAND combination.

On Mon, 13 Feb 2006 18:44:26 -0800, "Bill Adkins" Bill
wrote:

I am trying to create a contact database where both the primary contact and
the spouse can both be clients. They need to be listed as separate contacts
since they can both own products, but I also need to link to each other. I
prefer to do this in the same table but I worry about this creating two
listings for each HUSBAND/WIFE and WIFE/HUSBAND combination.

It depends on how thoroughly you want to do this. If each contact will
be linked to either zero or one, no more, other contacts, you can put
a SpouseID field in the table; leave it null for those curmudgeonly
bachelors. This is somewhat redundant as you must fill in Desi in
Luci's record, and fill in Luci into Desi's, or risk having several
episodes' worth of misunderstandings result!

Another way is to implement a "many to many self join" with a
Relations table: it would have records for FirstID, SecondID,
Relationship. Again, you need two records to get the symmetry, or else
to use a slightly fancier query joining the Relationships table twice,
once each direction.

Finally, you might consider a Households table (perhaps containing the
mailing address, likely to be the same for both) related one-to-many
to your Customers table. This would have the advantage of symmetry
(everybody, even bachelors, belongs to one and only one household) and
flexibility (husband, wife, live-in parents, adult offspring etc. can
all be added).

John W. Vinson[MVP]

John I like the household idea. I really appreciate your help

For mail merge purposes I have no problems combining their names.

[firstname1] & " " & [lastname1] & " " & "and" & " " & [firstname2] & " " &
[lastname2]
or
[firstname1] & " " & "and" & " " & [firstname2] & " " & [lastname2]

How can I write it so that if the Last names are the same it reads
John and Judy Smith
but if the last names are different it will read
John Smith and Judy Johnson

"John Vinson" wrote:

On Mon, 13 Feb 2006 18:44:26 -0800, "Bill Adkins" Bill
wrote:

I am trying to create a contact database where both the primary contact and
the spouse can both be clients. They need to be listed as separate contacts
since they can both own products, but I also need to link to each other. I
prefer to do this in the same table but I worry about this creating two
listings for each HUSBAND/WIFE and WIFE/HUSBAND combination.

It depends on how thoroughly you want to do this. If each contact will
be linked to either zero or one, no more, other contacts, you can put
a SpouseID field in the table; leave it null for those curmudgeonly
bachelors. This is somewhat redundant as you must fill in Desi in
Luci's record, and fill in Luci into Desi's, or risk having several
episodes' worth of misunderstandings result!

Another way is to implement a "many to many self join" with a
Relations table: it would have records for FirstID, SecondID,
Relationship. Again, you need two records to get the symmetry, or else
to use a slightly fancier query joining the Relationships table twice,
once each direction.

Finally, you might consider a Households table (perhaps containing the
mailing address, likely to be the same for both) related one-to-many
to your Customers table. This would have the advantage of symmetry
(everybody, even bachelors, belongs to one and only one household) and
flexibility (husband, wife, live-in parents, adult offspring etc. can
all be added).

John W. Vinson[MVP]