Solved

Check 3 tables have used same Contact ID

Posted on 2014-01-06
13
295 Views
Last Modified: 2014-01-06
Hi

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.
0
Comment
Question by:CptPicard
13 Comments
 
LVL 84
ID: 39759132
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
  Else 
    '/ The ContactID currently showing on the Form exists in all 3 tables
  End If
End If

End Sub

Open in new window

0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39759140
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

or

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

Author Comment

by:CptPicard
ID: 39759162
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.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:CptPicard
ID: 39759169
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
0
 
LVL 26

Expert Comment

by:tigin44
ID: 39759181
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
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39759185
Get rid of the final "UNION  ALL"

Make sure your replace "tbl1", "tbl2", "tbl3" with the actual names of your tables
0
 

Author Comment

by:CptPicard
ID: 39759223
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.
0
 

Author Comment

by:CptPicard
ID: 39759234
tigin44 - when I use your sql I get the following error

Syntax error in FROM clause
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39759247
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).
0
 

Author Comment

by:CptPicard
ID: 39759258
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39759268
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.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39759280
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)
0
 

Author Closing Comment

by:CptPicard
ID: 39759418
Thanks Fyed. with a little bit of tweaking this has worked great. Thank you.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question