Link to home
Start Free TrialLog in
Avatar of jalant
jalant

asked on

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

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?
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

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
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
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
Avatar of jalant
jalant

ASKER

Thanks Dale - I will try this out over the weekend and report back. Appreciate your help!
I guess I don't get the reason for the query. If you have the Y/N field, just use that without coercing it.
Avatar of jalant

ASKER

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
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.