Solved

Check 3 tables have used same Contact ID

Posted on 2014-01-06
13
291 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:CptPicard
Comment Utility
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
Comment Utility
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)
Comment Utility
Get rid of the final "UNION  ALL"

Make sure your replace "tbl1", "tbl2", "tbl3" with the actual names of your tables
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:CptPicard
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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)
Comment Utility
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)
Comment Utility
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
Comment Utility
Thanks Fyed. with a little bit of tweaking this has worked great. Thank you.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now