Contact Database - relational

  • Thread starter Thread starter Sarah Becker
  • Start date Start date
S

Sarah Becker

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
 
Try

tblContacts
ContactID - primary key
ContactName
etc

tblAddresses
AddressID - primary key
Address
etc

tblContactAddress
ContactID - foreign key to tbl Contacts
AddressID - FK to tbl Addresses

Then two ( or more ) people living at the same address are 'related' by
having the same addressID in the tblContactAddress table.

So a query pulling the addresses will normally return both contacts at the
same address, but you can set the DISTINCT clause and just return one of
them for actual mailouts.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
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
|
 
Back
Top