compare two fields and to see if they match

Question

compare two fields and to see if they match

Anonymous

Hi All,

I need some advice, once again!

I want to run a query that pulls data from two different queries and compares one field to another and if it is the same between tables returns 1 and if not the same returns 0.

These are my two queries: There is one field “studentid” that is the same in both tables, that I will join the queries on.

Table1query has the following fields:

StudentID, test1, pass1

Table2query has the following fields:

StudentID, test2, pass2

Test2 is a numerical (1,2,3, etc.)

Pass1 and pass2 is either a 1 (for pass) or 0 (fail).

I want my query to compare pass1 and pass2 and if they are equal return a new field with 1 for equal and 0 for unequal or some other numbers.

I looked at the compare function but that didn’t seem like a good one to use. It must be somewhat simple to do, but I'm still a bit of an Access newbie.

Thanks,

BSky

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Answer 1

SELECT StudentID, Pass1, Pass2, IIF([Pass1]=[Pass2],1,0) AS Match

INNER JOIN on Table1query.StudentID = Table2query.StudentID

FROM Table1query, Table2query

ORDER BY StudentID;

Or, in Query Design mode add the two queries, Join on StudentID, Add the StudentID, Pass1 and Pass2 columns, then add a 4th column with the expression:

Match: IIF([Pass1]=[Pass2],1,0)

Hope this helps, Scott<> P.S. Please post a response to let us know whether our answer helped or not. Microsoft Access MVP 2009 Author: Microsoft Office Access 2007 VBA Technical Editor for: Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports and Queries

Answer 2

Anonymous

Thanks Scott,

I'll try it and let you know!

BSky

0 comments

Share via

compare two fields and to see if they match

2 answers