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?
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?
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
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
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.
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
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 TRIALMembers 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.
FROM TableTwo as T2
LEFT JOIN TableOne as T1
ON T2.NPI = T1.NPI