• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

QC check between two tables

I have two tables and would like to have a query that looks at the Name field in both tables to see if they match or dont and display the findings.

Primary table is table one
  • 4
  • 3
1 Solution
how to the 2 tables join (what fields)?

select t1.name, t2.name
from (table1 t1
inner join table2 t2 ON t1.id = t2.id)
CMILLERAuthor Commented:

In table one, I have a specific name showing up. In table two they dont. I need a query that looks at both tables and displays the names that dont show up in table two.
:) yes
but how to the 2 tables relate to each other?
(what are the fields involved in the join?)

Perhaps you could list the fields and we can take an educated guess.

and suggestion:  
use the actual table names and field names, it will be easier for you in the long run as the code we suggest can be tried without "translation"
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

CMILLERAuthor Commented:
I dont have them joined yet. We could use SSAN.

Table one is "Mandoc" table two is "MDCPDS"
My Access skills are super rusty but I think this will work
select Mandoc.name, MDCPDS.name
from (Mandoc
inner join MDCPDS ON Mandoc.SSAN= MDCPDS.SSAN)
where Mandoc.name <> MDCPDS.name

Open in new window

by the way, if "<>" doesn't work try "!="

{+ edit, got SSAN wrong, fixed}
CMILLERAuthor Commented:
it found some names but the issue is that the person that I know doesnt exist in Table 2 - MDCPDS is not showing up in the query.

there is no record for the person in table 2, he only has a record in table one.
a note on the join used above (inner join)

that join will ONLY locate SSAN values that are matched in BOTH tables
If there is a possibility that records exist in one table but not the other, then we need an "outer" join. If you use the query design view and drag SSAN's to form a join (defualts to an inner join), you can then alter the nature of the join to outer along these lines:

In the Diagram pane of the Query Designer, drag the name of the join column in the first table or query and drop it onto the matching column in the second table or query.
In the Diagram pane, right-click the join line, and then click Properties.
On the Join Line tab, under Include rows, do one of the following:
   To create a left outer join, select the first check box — for example, All rows from Mandoc.
   To create a right outer join, select the second check box — for example, All rows from MDCPDS.
   To create a full outer join, select both check boxes — for example, All rows from Mandoc and All rows from MDCPDS.
( quote is derivation from office.microsoft.com )
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now