Circular Reference (well, kind of)…

You sat down to build your database and things were going great.  You made a table for Members and one for Members Contacts.  Only you now realize that the Members Contacts can also be Members.  At first you think… that will be okay.  But then you go to build your forms and realize this is a problem.  If you create a UNION query to show all the Members, you can’t edit the information AND you notice that some Member’s ID is the same as some Member Contacts ID.

This is when you need to think outside the box.  You can store all your Members, Company’s, Contacts and Individuals, in the same table and still keep track of Member’s Contacts which will make querying much easier.  But let’s be clear, this is the exception to the rule, not the rule.  Normally, I would recommend a table for Company’s and a seperate table for Contacts.  However, we all know in the real world the *standard* does not always apply!

For this example we’ll use the table as outlined below (To see the complete Data Model and download the Sample Database, click here)…

tblMembers
mActive
mMemberID (PK)
mRegistrationID
mAttachedTo (circular reference to Member ID in this table)
mCompanyName
mRegistrationID
mTitle
mFirstName
mLastName
etc…

In the table we’ll enter…

Figure 1

As you can see Access Diva (Company) has a MemberID of 1.  Gina Whipp, who works for Access Diva but is also a Member has a MemberID of 2.  In order to *relate* Gina to the Company in the mAttachedTo field you would enter a 1 or, since this would be on a Form with a Combo Box to select from, you would select Access Diva (Figure 2).

Figure 2

The RowSource for the Combo Box in Figure 2 is…

SELECT tblMembers.mMemberID, tblMembers.mCompanyName FROM tblMembers WHERE (((tblMembers.mAttachedTo) Is Null));

You can either build one Form to enter Members OR you could build a form that has a Main Form for Company’s and Subform for Company Contacts.  Using the Main Form/Subform scenario you could use the mMemberTypeID or the mRegistrationID to identify which Contacts are also Members.  To seperate the two is easy… Take a look at the following two queries…

Companies…

SELECT tblMembers.mMemberID, tblMembers.mCompanyName, tblMembers.mFirstName, tblMembers.mLastName
FROM tblMembers
WHERE (((tblMembers.mAttachedTo) Is Null));

Contacts/Individuals…

SELECT tblMembers.mMemberID, tblMembers.mCompanyName, tblMembers.mFirstName, tblMembers.mLastName
FROM tblMembers
WHERE (((tblMembers.mAttachedTo) Is Not Null));

Download the Sample Database here…  Complaints.zip

 1,771 total views,  1 views today