Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 135
  • Last Modified:

2 queries, 1 checks other for matching field and returns "yes"

I have two access queries.

Query 1: List of peoples names plus a reference number.
Query 2: A list of reference numbers.

In query 1 i'd like to add another column that simply returns Yes and No.
Yes if the reference number matches any reference number in Query 2.
No if no match is found.

How do I do this?
0
antonioking
Asked:
antonioking
1 Solution
 
plusone3055Commented:
Use an UPDATE statement
0
 
mbizupCommented:
Try this:

SELECT q1.*,  IIF(q2.RefNumber IS NULL, "NO", "YES" ) AS RefInQuery2
FROM Query1 q1 LEFT JOIN Query2 q2 ON q1.RefNumber = q2.RefNumber

Open in new window

0
 
peter57rCommented:
Your version of....

SELECT query1.*, IIf(IsNull([query2].[reference]),"No","Yes") AS Matched
FROM Query1 LEFT JOIN Query2
ON Query1.Reference = query2.Reference;
0
 
plusone3055Commented:
First things first you have to run the 2nd query to get all the reference numbers and put them in a dummy table or a temp table

THEN do an update statement on the data table that query 1 is running against

so lets say that query 1 returns data from table1
query 2 returns data from table 2


UPDATE
[table1]
SET
column(yes/no) =
(
CASE
WHEN
(table1.id = table2.id)
'YES'
ELSE
column(yes/no) = NO
END
)
WHERE
table1.id <> NULL
0
 
antoniokingAuthor Commented:
Perfect, thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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