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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

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

Open in new window

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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:
Found the solution:

WHERE (IIf(EXISTS(SELECT * FROM query1 WHERE Field1=Field2), Field1=Field2, Field2=""))

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tim313Author Commented:
I found the correct solution before any correct solution was submitted by others.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.