Return Records If one Field is true and another False

[SuperKoopa]

Hello!

I am trying to create a query pulling from several tables. I will use the example below to illustrate what I'd like to do.

I have a field called 'Acc_Num', one called 'Stat_Code', and a third called 'Cat_Code'.

Each 'Acc_Num' can have multiple records because there are multiple Stat_Code and Cat_Code values.

What I want to do is isolate just the Acc_Num records where Stat_Code = 1 and Cat_Code equals A1.

I also want to isolate the Acc_num records where the Stat_code = 1 and where Cat_code does not exist.

This is the best I can really explain it, any assistance would be greatly appreciated. Thank you very much for reading!

 

[plog]

In design view of the query, underneath the Stat_Code field, put 1 as the criteria. Then underneath Cat_code put this:

Isnull OR "A1"

If that doesn't provide you what you need, post sample data from your tables, what your query is returning based on that sample data and what the query should return based on that sample data.

 

[SuperKoopa]

Hi Plog,

Thank you very much! That seems to work just fine! One thing I forgot to mention however in my original post, but I need only the records where the Stat_Code = 1 AND 5. So for instance there are TWO Acc_Num Records for one acc value, one is for Stat_Code 1 and the other is for Stat_code 5. Is there a way to make one record for each time both stat_code 1 and 5 exist?

Example: If Stat_Code 1 AND 5 exists then ONE single record showing "Both" populates. If just Stat Code 1 Exsists then "1 Only" populates. If Stat_Code 5 exists then "5 Only" populates.

Even if it doesnt show up the way the example above illustrates, if I can get it to only show me results where both exsists for the Acc_num record then that should be good enough.

I hope this followup doesnt constitute a forum violation, I just want to make sure I can (or cant) get this query to pull propery.

 

[Swatkins]

In this example, I believe you can achieve what you want by:

1) Unchecking "Show" under Stat_Code and Cat_Code.
2) Open the Property Sheet for the query, find the field "Unique Values" and make sure it says "Yes".

That should generate a list only of the unique Acc_Num values that match the criteria given for Stat_Code and Cat_Code.

 

[plog]

Gonna need some sample data. Post example of data in your table(s) and then what you want your query to show based on that sample data. Be sure to include table and field names. Use this format for posting your data:


Table1NameHere
Field1NameHere, Field2NameHere, Field3NameHere
David, 12, 4/3/2008
Steve, 8, 5/16/2010
Amy, 2, 6/6/2007

 

[SuperKoopa]

Thanks for your replies!

Example:

Query Design
Field: Acc_Num, Stat_Code, Cat_Code
Table: Acc.Db, Acc.Db, AccStep.Db

Table View -
Table Name: Dual Stat Query

Acc_Num, Stat_Code, Cat_Code
0012345678, 1, A1
0012345678, 5, A1
1123456789, 1, A1
2234567890, 5, A1

So in this example I would like the query to show one record of 0012345678 as follows:

0012345678, Both, A1
​

And record 1123456789 and 2234567890 should only show one record each as follows:

1123456789, 1 Only, A1
2234567890, 5 Only, A1
​

So those 2 accounts dont have both 1 AND 5 stat_codes exsisting in the query, just one or the other where as 0012345678 has both 1 and 5.

 

[plog]

This SQL will accomplish what you want:

SELECT Acc.Acc_Num, IIf(Min([Stat_Code])=Max([Stat_Code]),Max([Stat_Code]),"Both") AS StatCodes, Acc.Cat_Code
FROM Acc
WHERE (((Acc.Stat_Code)=1 Or (Acc.Stat_Code)=5))
GROUP BY Acc.Acc_Num, Acc.Cat_Code
HAVING (((Acc.Cat_Code)="A1"));

 

[Swatkins]

If what you want is output that includes the designation "Both" then you'll need to do something a little extra. (If all you care about is getting the list of Acc_Num, then the notes I highlighted above will get you that.)

To get that "Both" designation, I believe what you'll need is to create two queries, and then a third query that queries from those two.

The first two will each query for Stat_Code criterion of 1 and 5 respectively.

In the joined query of the two, you'll pull the Stat Code from both lists. Your "Acc_Num" will have to be joined, and you'll have 2 Stat_Code fields (you may want to use an alias to differentiate their names like "Stat_Code1" and "Stat_Code5"). Then you'd have a separate calculated field that checks if both the the Stat_Codes are filled, and report "Both" if so, or report "1 Only" or "5 Only" if one or the other is present.

So, for example:

qryStatCode1
Field: Acc_Num
Table: Acc.Db
Show: Yes
Field Stat_Code
Table: Acc.Db
Show: Yes
Criteria: 1
Field: Cat_Code
Table: AccStep.Db
Show: Yes
Criteria: A1

qryStatCode2
Field: Acc_Num
Table: Acc.Db
Show: Yes
Field Stat_Code
Table: Acc.Db
Show: Yes
Criteria: 5
Field: Cat_Code
Table: AccStep.Db
Show: Yes
Criteria: A1

qryStatCodeBoth

Tables: qryStatCode1, qryStatCode5 (Acc_Num are linked on both)
Field: Acc_Num
Table: qryStatCode1 (this can be from either if the values are linked)
Show: Yes
Field: Stat_Code as StatCode1
Table: qryStatCode1
Show: No
Field: Stat_Code as StatCode5
Table: qryStatCode5
Show: No
Field: State_Code Both: IIF(IsNull(StatCode1), "5 Only", IIF(IsNull(StatCode5), "1 Only", "Both")
Show: Yes
Field: Cat_Code
Table: qryStatCode1
Show: Yes
Criteria: A1

The syntax above may be a bit dodgy, but should push you in the right direction.