Check 3 tables have used same Contact ID


I use Access 2010.

I have 3 tables that hold a lot of information but all 3 of these tables has a field called ContactID.

I would like to know how I can check if the same ContactID has been used in tbl1, tbl2 & tbl3. Basically, if the same ContactID has been used in all three of these tables, I need to display within a text box within a form, a message to show the total number that are complete.  If the ContactID has only been used in tbl1 & tbl2 for example, then it would not take this into account as all 3 tables need to have the same ContactID in order for it to be complete.

Hope this makes sense.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You could perhaps do something in the Current event of your form, which will fire as the user moves through the records on that form:

Sub Form_Current()
  '/ don't check for New Records
  If Me.NewRecord Then Exit Sub

  Dim rst As DAO.Recordset
  Set rst = Currentdb.OpenRecordset("SELECT Table2.ContactID AS T2, Table3.ContactID AS T3 FROM Table1 INNER JOIN Table2 ON Table1.ContactID=Table2.ContactID INNER JOIN Table3 ON Table1.ContactID=Table3.ContactID WHERE ContactID=" & Me.ContactID)

If Not (rst.EOF and rst.BOF) Then
  If Nz(rst("T2"), 0) = 0 OR Nz(rst("T3"),0) = 0 Then
    '/ Table2 or Table3 does not contain the ContactID currently showing on the Form
    '/ The ContactID currently showing on the Form exists in all 3 tables
  End If
End If

End Sub

Open in new window

Dale FyeOwner, Dev-Soln LLCCommented:
You could create a query like:excuse the image, my security settings block Union queriesSave this query as qryContactIDUse, then you could use a DCOUNT ( ) function to determine how many tables the ContactID is in:

Dcount("*", "qryContactIDUse", "[ContactID] = 1")    'if ContactID is numeric


Dcount("*", "qryContactIDUse", "[ContactID] = 'ContactName'")   'if ContactID is string.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CptPicardAuthor Commented:
Hi fyed - I'm created this query and entered the above SQL in the SQL View but I get the following error message:

syntax error in query.  Incomplete query clause.
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

CptPicardAuthor Commented:
This is what I entered.

SELECT DISTINCT "1" as tblNum, ContactID FROM tbl1 UNION ALL
SELECT DISTINCT "2" as tblNum, ContactID FROM tbl2 UNION ALL
SELECT DISTINCT "3" as tblNum, ContactID FROM tbl3 UNION ALL
simply the following query gives the ContactID that are used all of the tables.

select ContactID
from table1 t1
          table2 t2 on t1.ContactID = t2.ContactID
          table3 t3 on t1.ContactID = t3.ContactID
Dale FyeOwner, Dev-Soln LLCCommented:
Get rid of the final "UNION  ALL"

Make sure your replace "tbl1", "tbl2", "tbl3" with the actual names of your tables
CptPicardAuthor Commented:
I've done all this and it displays a number but it's displaying the wrong number.  I've just done a test and added the same ContactID in all three tables for just 1 person and it's showing the number 3 when it should be showing the number 1.
CptPicardAuthor Commented:
tigin44 - when I use your sql I get the following error

Syntax error in FROM clause
Dale FyeOwner, Dev-Soln LLCCommented:
No, it should show 3, because you added the ContactID to all three tables.  If you remove that ContactID from one of the tables, that DCOUNT() statement will return 2 (the number of tables that the ContactID is found in).
CptPicardAuthor Commented:
I understand but I actually only want it to display the number 1 if all 3 tables are completed with the same ContactID because the number 1 means it's completed.
Dale FyeOwner, Dev-Soln LLCCommented:
tigin44's syntax might work in SQL but in Access, you will require parenthesis and INNER JOIN clauses.  If you want to try this, drag the three tables into the query grid, create joins on the ContactID field from tbl1 to tbl2 and from tbl1 to tbl3

Then add a field (ContactID) to the query grid.  It will return a list of all the ContactIDs that are in all three tables.  But if you use the ContactID multiple times in the same table, there will be duplicates.
Dale FyeOwner, Dev-Soln LLCCommented:
I understand but I actually only want it to display the number 1 if all 3 tables are completed with the same ContactID because the number 1 means it's completed

If you are doing this on a form, you could add an unbound checkbox (chk_Complete) to the form and in the Form_Current event, you could do something like:

me.chk_Complete =  (Dcount("*", "qryContactIDUse", "[ContactID] = 1")  = 3)
CptPicardAuthor Commented:
Thanks Fyed. with a little bit of tweaking this has worked great. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.