Incorrect Query Results

[cpberg1]

SELECT CFI11Basic.CFI11LastName, CFI11Basic.CFI11FirstName, Count(STD21Basic.STDID) AS CountOfSTDID, STD21Basic.STD21Status
FROM (CFI11Basic LEFT JOIN STD21Basic ON CFI11Basic.CFIID = STD21Basic.CFIID) LEFT JOIN STDStatus ON STD21Basic.STD21Status = STDStatus.STDStatus
GROUP BY CFI11Basic.CFI11LastName, CFI11Basic.CFI11FirstName, STD21Basic.STD21Status, CFI11Basic.CFI11Airplane, CFI11Basic.CFI11MasterDisplay, CFI11Basic.CFI11Status
HAVING (((Count(STD21Basic.STDID))=0) AND ((CFI11Basic.CFI11Airplane)=True) AND ((CFI11Basic.CFI11MasterDisplay)=True) AND ((CFI11Basic.CFI11Status)=1)) OR (((STD21Basic.STD21Status)=4) AND ((CFI11Basic.CFI11Airplane)=True) AND ((CFI11Basic.CFI11MasterDisplay)=True) AND ((CFI11Basic.CFI11Status)=1));

I'm pretty sure I'm getting exactly what I'm asking for but not what I need!


I need CFI Name and Count of STDID (only count arrived) when STDStatus = "arrived".

I get CFI Name and Count of STDID --> if Count<>0 and STDStatus = arrived.

I get CFI Name and Count of STDID --> If Count = 0

I cannot figure out how to get CFI Name and Count of STDID = 0 (Because they have students but not with STD Status = arrived)

Appreciate any help with proper syntax.

 

[spikepl]

Actually, for aggregating queries if you want a WHERE in the QBE you just add the field one more time, and select WHERE in the Total row of the same column, and add the criterion in the Criteria row. That automatically unticks the Show chekbox , and leads to the same end result as described by Pat.

 

[cpberg1]

Still haven't solved this one. I'm going to try to rephrase.

Give a list of all teachers with a status of 1.
For all those teachers find how many students with status of 4.

If they have 0 students I can get them to come up, if they have students with status of 4 I can display them. I cannot display the teacher name if they have students with status <>4.

For my latest go at this problem I've tried to base my form on two queries
1. Give a list of all teachers with a status of 1.
2. For all those teachers find how many students with status of 4.