Match values and append Y/N to tables in Access.

jalant
jalant used Ask the Experts™
on
I have two tables in Access: Table one has two fields: ID and NPI. The second table has 3 fields: ID, NPI, and Match (Y/N).

I need to create a relationship between the NPI in both tables, and then generate a query that looks like the 2nd table, but where Y or N is put in the last field (Match N/Y) depending on whether there is a match between the two tables (Y) or not (N).

Can this be done in Access?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
SELECT T2.ID, T2.NPI, IIF(IsNull(T1.ID), "N", "Y") as Matched
FROM TableTwo as T2
LEFT JOIN TableOne as T1
ON T2.NPI = T1.NPI
Most Valuable Expert 2012
Top Expert 2014

Commented:
whether there is a match between the two tables (Y) or not (N).
I don't really follow ... how are you matching, and what do you do when you've matched? Do you simply want to show records in the Second table that do NOT match any in the First table? If so:

SELECT * FROM Table1 LEFT OUTER JOIN Table2 ON Table1.NPI=Table2.NPI WHERE Table2.ID IS NULL

That would show you any record in Table1 that has no match on the NPI field in Table2

If you want to show all records where a match is found on NPI:

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.NPI=Table2.NPI
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Scott,

see OP's original post:  "a query that looks like the 2nd table, but where Y or N is put in the last field (Match N/Y)"

my emphasis
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Author

Commented:
Thanks Dale - I will try this out over the weekend and report back. Appreciate your help!
Most Valuable Expert 2012
Top Expert 2014

Commented:
I guess I don't get the reason for the query. If you have the Y/N field, just use that without coercing it.

Author

Commented:
Let me simplify (not exactly my situation, but same idea):

I have two tables, T1 & T2. Both tables have a single field called NPI. I need to create a query that will display all the records in T2, and then create another field that has Y or N depending on whether the NPI appears in T1.

Example:

T1
3
6
7
8
12
14

T2
1
2
3
4
5
6
7
8
9
10
11
12
13
14


Query should return:
1 N
2 N
3 Y
4 N
5 N
6 Y
7 Y
8 Y
9 N
10 N
11 N
12 Y
13 N
14 Y

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial