MS Access Query Help Needed

I have a query that can return one, two or three records. I need to query this query based on comparison of two fields (Field1, Field2).

Field1 always has data (text).
Field2 can have data (text) or be null.

Possible returns from the query are:
     One record returned:
          1.   Field2 is null
     Two records returned:
          1.   Field1 = Field2
          2.   Field2 is null
     Three records returned:
          1.   Field1 = Field2
          2.   Field2 is null
          3.   Field1 <> Field2

Of these possible scenarios, I need the new query to return the single record indicated by the number 1.

Can someone provide a solution?

Thanks in advance,

Who is Participating?
Tim313Connect With a Mentor Author Commented:
Found the solution:

WHERE (IIf(EXISTS(SELECT * FROM query1 WHERE Field1=Field2), Field1=Field2, Field2=""))
SharathData EngineerCommented:
Can you porovide some sample data from the table and expected result? I did not understand your scenarios completely.
Tim313Author Commented:
Thanks for the reply,

The query can return a set of results which may have one row, two rows or three rows as shown:

Result -1 record returned        Result - 2 records returned        Result - 3 records returned

            Field1   Field2                                 Field1     Field2                          Field1     Field2
Row1      A       <Null>                      Row1     A             A                  Row1      A             A
                                                            Row2     A         <Null>             Row2      A         <Null>
                                                                                                                Row3      A             B

I need a new query to query the above possible results so I get 1 record returned as follows:

If  ([Field1] = A and [Field2] = A)  then return this record only.

If no ([Field1] = A and [Field2] =A) in the set, then return ([Field1] = A and IsNull([Field2]))

I do not want ([Field1] = A and [Field2] = B) to be returned.

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

SharathData EngineerCommented:
try this query.
select *
  from your_table
 where Field1 = Field2 or Field2 is null

Open in new window

Tim313Author Commented:
Thanks for your reply Sharath_123,

If the first query returned 2 or 3 records, the criteria "where Field1 = Field2 or Field2 is null" in the second query would return 2 records:

     Field1 = A, Field2 = A     AND     Field1 = A, IsNull(Field2)

I'm thinking the WHERE needs to be more specific... possibly needing nested "IIf" statements.

SharathData EngineerCommented:
Post some sample data and the expected result. That will really help.
Tim313Author Commented:
I found the correct solution before any correct solution was submitted by others.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.