Solved

Check 3 tables have used same Contact ID

Posted on 2014-01-06
13
296 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 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