Access MVP (2010-2015)

oOo.oOo.oOo.oOo.oOo.oOo

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…

tblComplaints

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).

MemberContacts

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

108 total views, 1 views today

7 comments to Circular Reference (well, kind of)…

  • Tony Williams

    Thanks Gina

  • Tony Williams

    Think I cracked it I put this in the forms properties:
    Private Sub Form_AfterInsert()
    Me.Combo0.Requery
    End Sub
    And it seems to work 🙂
    Now for the rest of the form
    Tony
    PS Sorry, should have tried all those things before I posted, lesson learnt 🙁

    • I see you didn’t need me after all…

      I also see, by your last comment, that the sample I uploaded is missing the Forms! I will fix that later today as I must have uploaded the one from my website which only includes the tables. Sorry about that but THANKS for pointing that out!

  • Tony Williams

    Hi Gina, I did download that example but there are only tables and a couple of queries, no forms?
    I created one of my own based on your website example.I have got it to work,just need to figure out how to update the combobox immediately after I’ve entered a new company. So that if the next new record is to be attached to the new company record it’s in the list. I’m going to try Docmd.Requery in a few places.

    I love challenges!
    Tony

  • Hello Tony,

    Yes, the Control Source for the *Relate To* control is *mAttachedTo*. To see a sample, at the bottom of the article, is a downloadable file *Complaints.zip*, it has a sample Form demonstrating what I described.

  • Tony Williams

    Hi Gina What would be the Control source for the Relate to control, would it be mAttachedTo? Any chance including this form in the zip file?
    Thanks
    Tony