Help with Query Design for desired Outcome

[springwood01]

This should be easy, but I'm looking for advise on quickest/easiest way to get results I need. Initial query with SQL is below. I have a criteria of A or B in CatID field. This gives me a record of all persons who have a no for either the A or B category. I need to generate 3 reports from my query/s:

1. FullNames that have no for both A & B id's.
2. FullNames that have a no for A but not listed in #1 report.
3. FullNames that have a no for B but not listed in #1 report.

Tried Criteria A AND B to get report#1 and did not work. Tried several other things to get the 3 results I need but just can't get there. Looked at an IIF statement but unable to determine the format for that which works. Any suggestions would be helpful.


SELECT AutoCamNo.FullName, AutoCamNo.ActiveContractNum, AutoCamNo.LastOfCompliant, AutoCamNo.CatID, AutoCamNo.CatName
FROM AutoCamNo
GROUP BY AutoCamNo.FullName, AutoCamNo.ActiveContractNum, AutoCamNo.LastOfCompliant, AutoCamNo.CatID, AutoCamNo.CatName;

 

[springwood01]

OPPs, gave you incorrect SQL for problem. Here is the correct one:

SELECT AutoCamNo.FullName, AutoCamNo.ActiveContractNum, AutoCamNo.LastOfCompliant, AutoCamNo.CatID, AutoCamNo.CatName
FROM AutoCamNo
GROUP BY AutoCamNo.FullName, AutoCamNo.ActiveContractNum, AutoCamNo.LastOfCompliant, AutoCamNo.CatID, AutoCamNo.CatName
HAVING (((AutoCamNo.CatID)="A" Or (AutoCamNo.CatID)="B"));

 

[randy700]

Please provide some sample data.

Randy

 

[Golom]

Your terminology is a bit confusing.
You state

I have a criteria of A or B in CatID field

but then you go on with

... persons who have a no for either the A or B category...

I'm assuming in the following that
[tt]
CatID <> "A" ---> means "No" for the "A" category
CatID = "A" ---> means "Yes" for the "A" category
[/tt]
If that's correct then

FullNames that have no for both A & B id's.

SELECT FullName, ActiveContractNum, LastOfCompliant, CatID, CatName
FROM AutoCamNo
WHERE CatID NOT IN ('A','B')

The rest is somewhat contradictory.

Report #1 excludes "CatIDs" that have the values "A" or "B" (i.e. Records with EITHER value for CatID are excluded.)

Report #2 Excludes records where CatID = "A" and those records have also been excluded from Report #1.

Thus the records in Report #2 that do not exist in Report #1 must be the records where CatID = "B". Soooo ...

Report #2

SELECT FullName, ActiveContractNum, LastOfCompliant, CatID, CatName
FROM AutoCamNo
WHERE CatID = 'B'

And by similar reasoning
Report #3

SELECT FullName, ActiveContractNum, LastOfCompliant, CatID, CatName
FROM AutoCamNo
WHERE CatID = 'A'

 

[springwood01]

Golom, please note the SQL correction I sent before I got your response. My 1st query gives me 315 records of people with a "NO" in the categoryID field for either categories A or B. In some cases, the same person could be listed twice as they have a "no" for both A & B. For report #1 I need to determine from the listings which people have a no for both A and B. I know there are some and I need to identify who they are. Since report #1 is for those with a no in both A & B, who then are the people that only are no in B (report2) and then (report3) who only shows up as a no in A. Make sense?

 

[PHV]

Something like this ?
1)
SELECT FullName, ActiveContractNum, LastOfCompliant, CatID, CatName
FROM AutoCamNo
WHERE FullName NOT IN (SELECT DISTINCT FullName FROM AutoCamNo WHERE CatID In ('A','B'))
2)
SELECT FullName, ActiveContractNum, LastOfCompliant, CatID, CatName
FROM AutoCamNo
WHERE FullName NOT IN (SELECT DISTINCT FullName FROM AutoCamNo WHERE CatID = 'B')
AND FullName IN (SELECT DISTINCT FullName FROM AutoCamNo WHERE CatID = 'A')
3) is easily derived from 2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Not really ... until we can agree on some terminology ...

I'm still a bit at a loss about your use of phrases like

... "NO" in the categoryID field ...

What does the categoryID field actually contain? Is it values like "A" or "B" or is it boolean values like "Yes" or "No"? Your SQL would indicate that it is "A" or "B" but I'm not sure about that given your words.


In your text you talk about

... FullNames that have ...

which lead me to believe that you were concerned with only the "FullName" field. Your SQL however has

GROUP BY FullName, ActiveContractNum, LastOfCompliant, CatID, CatName

That results in a record for each unique combination of those fields ... not just for each unique "FullName".

Your second SQL sample will return only those records where CatID = "A" OR catID = "B" ... yet you said of Report #1

FullNames that have no for both A & B id's

Am I to infer that "catID = 'A'" is to be interpreted as "No" for "A" ID?


Assuming that your SQL is correct (i.e. catID really does contain 'A', 'B', etc.) then a query to select those records where the catID is neither 'A' nor 'B' would be

Select FullName
From   AutoCamNo
Where  catID NOT IN ('A','B')
Group By FullName

and you will get only one record for each "FullName

 

[randy700]

I, too, have been confused by your wording. Following up on Golom's line of reasoning.....

You say you want a report listing the records that have NO for both "A" and "B". That would indicate 2 boolean fields that both return false. If, however, you have only 1 field (CatID), which contains only "A", "B", or Null.... how do you yield a result of NO for both "A" and "B"?




Randy

 

[springwood01]

Sorry I rushed my response back but I was being called into a meeting. Let me give you sample data so you can see what I am working with. This comes from my Corrected SQL above that gives me all the A & B's that have a no listed. The NO means they did not meet the criteria for that health category. In sample below, John Doe and Jack Go did not meet criteria in either A or B (report #1), Mary did not meet A but since B is not listed, she met that and would not be on report #1 but would be on #2. Same concept with Jane who did not meet B but did meet A so needs to be on report #3 as she would not be on #1.

FullName ActiveContract# LastOfCompliant CatID CatName

John Doe 123456789 NO A HRA
John Doe 123456789 NO B Screen
Mary Joe 000111222 NO A HRA
Jane Eye 222333444 NO B Screen
Jack Go 777888999 NO A HRA
Jack GO 777888999 NO B Screen

 

[springwood01]

This thread can be closed as I have figured it out and got to what I wanted with minimal query activitiy. thanks everyone for your responses as it gave me some ideas to proceed.