Hi Sarah,
I take it that you would like to have 2 separate records, for example:
Person Name Spouse Name
John Doe Jane Doe
Jane Doe John Doe
Bob Smith
Joe Clark Mary Clark
Mary Clark Joe Clark
Bill Jones Mildred Jenkins
Mildred Jenkins Bill Jones
If your table is setup similar to the above then you could create a query
like the following:
SELECT Table1.PersonName, Table2_1.PersonName
FROM Table1 LEFT JOIN Table1 AS Table1_1 ON
Table1.PersonName=Table1_1.SpouseName;
The result of the query would be:
Table2.PersonName Table2_1.PersonName
Bill Jones Mildred Jenkins
Bill Smith
Joe Clark Sandra Clark
John Doe Mary Doe
Mary Doe John Doe
Mildred Jenkins Bill Jones
Sandra Clark Joe Clark
Problem with the above is you would have for example the family John & Mary
Doe appearing twice.
The only workaround I can think of at the moment is to have another table
that would serve as storing the Family head for each family. Example:
FamilyHead
Bill Jones
Bill Smith
Joe Clark
Mary Doe
Then if you have a query like the following:
SELECT Table1.PersonName, Table1.SpouseName
FROM Table1 INNER JOIN FamilyHead ON Table1.PersonName =
FamilyHead.FamilyHead;
Your results would be:
PersonName SpouseName
Bill Jones Mildred Jenkins
Bill Smith
Joe Clark Sandra Clark
Mary Doe John Doe
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<
http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <
http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."
This posting is provided "AS IS" with no warranties, and confers no rights
--------------------
| Content-Class: urn:content-classes:message
| From: "Sarah Becker" <
[email protected]>
| Sender: "Sarah Becker" <
[email protected]>
| Subject: Contact Database - relational
| Date: Wed, 10 Mar 2004 14:45:51 -0800
| Lines: 17
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcQG8XA84lBpwmOPTLKZpT7U/rP70g==
| Newsgroups: microsoft.public.access.tablesdbdesign
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.tablesdbdesign:76937
| NNTP-Posting-Host: tk2msftngxa11.phx.gbl 10.40.1.163
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| Hello-
| This may be very basic, but I do not know how to set up
| my database structure. I want to build something very
| simple that keeps track of personal & business contacts
| of mine. The problem is that, when someone is married I
| don't have any way of showing a relationship. For
| example, I could have John Doe and Jane Doe in my
| database, but nothing to link them together so that when
| I do a mail merge or something, they come out together
| ("Jane & John Doe"...Address...etc).
|
| Does that make sense? I considered creating a "male" &
| female" table, but it seems insufficient since I would be
| tracking the same exact data for each table. Thanks in
| advance for your help!!!
|
| Sarah Jones-Becker
|
