Solved

Check 3 tables have used same Contact ID

Posted on 2014-01-06
13
298 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 85
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 48

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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 48

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 48

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 48

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 48

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

695 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