Find records that meet 2 criteria in 2 fields

[grrltechie]

Ok, this is frying my brain. I'll start by saying I know Access decently, a tiny bit of VBA and SQL that is self taught, and have excellent Google Fu so I can fake it really well.
I have a list of accounts and I need to find the ones that have Insurance M (field one = M) as Primary (field two = Y) and also have Insurance X (field one = X) as secondary (field two = 2). I can almost imagine it but I can't figure out how to find both criteria at the same time. Is this possible? Or am I asking for too much?
In case it isn't obvious I work in healthcare so I can only post limited examples but I can provide a few records to show what I mean if that would help. I'm trying to help with an audit and this is like the first big step so if I can't figure out a way to do this I'm going to have to go to my vendor (ugh).
Thanks in advance for any help someone can throw my way

 

[CJ_London]

It may be very simple but sounds like all you need in the query grid is to put the first criteria on the same line c

In SQL this would equate to

(insurance =M and primary=y) OR (insurance =X and primary=2).

Your description is confusing so if you don't understand the above, please provide a few records as an example, identifying the ones you want returned. As with pictures, data is worth a thousand words. You can make up names for data and even your field names so long as they give an indication of what they are.

 

[plog]

The simplest way would be with 3 queries, 1 for each population, then JOIN them to see who is on both lists:

sub1 - SELECT PatientID FROM YourTable WHERE F1="M" AND F2="Y"

sub2 - SELECT PatientID FROM YourTAble Where F1="X" AND F2="2"

MainQuery - SELECT sub1.PatientID FROM sub1 INNER JOIN sub2 ON sub1.PatientID = sub2.PatientID

 

[theDBguy]

Hi. Welcome to AWF!

You're talking about two different accounts, correct? One with M and Y and the other with X and 2. Or, are you saying the same account could have both of those?

 

[grrltechie]

Hi. Welcome to AWF!

You're talking about two different accounts, correct? One with M and Y and the other with X and 2. Or, are you saying the same account could have both of those?

No it would be an account that has both, M insurance is primary (pays first) and the X insurance is secondary or pays second. I only want to find accounts that have both insurances in that order. If the account only has one insurance I don't want it or if the account has X insurance primary I don't want it.

 

[theDBguy]

No it would be an account that has both, M insurance is primary (pays first) and the X insurance is secondary or pays second. I only want to find accounts that have both insurances in that order. If the account only has one insurance I don't want it or if the account has X insurance primary I don't want it.

In that case, I think maybe you need an AND operator all throughout. For example:

...WHERE Field1="M" AND Field2="Y" AND Field1="X" AND Field2=2

However, looking at that, it doesn't seem like that's possible. How can Field1 have two different values for the same record?

 

[MarkK]

Yes. Always provide a few records to show what you mean. Maybe you are looking for parent rows that have child rows matching M and X.