I know the logic but I don't know how to do it

[Desperate]

This can be quite simple for you guys but I am confused and cant find how to do this.
I have 1 table and 1 query as below
Table has 2 fields, category elements and status column.
Status can be "FULL" or "NONE"

Query is a select query to GROUP BY table 1 category elements and has second field as overall status

If all status is FULL in Table 1 Query's overall status should be FULL
If all status is NONE in Table 1 Query's overall status should be NONE
If there is more than 1 FULL and NONE in Status, Overall status should be "PARTIAL"

In Example
TABLE
CATEGORY ELEMENTS----STATUS
A----------------- FULL
A----------------- FULL
B----------------- NONE
B----------------- NONE
B----------------- NONE
C------------------FULL
C------------------NONE

QUERY
CATEGORY----OVERALL STATUS
A--------------FULL
B--------------NONE
C--------------PARTIAL

The logic can be to take count of categories in table 1 and compare it with the amount of status.

Lets say there are 2 A Category so if There is 2 full, that will make overall category full or if there is 2 none overall will be none or if none and full are >1 overall status should be partial.

But how to do this by code or in query?
Help please!

 

[stopher]

I've attached an example of one way to do it. Take a look at both queries to see how we get there.

hth
Chris

 

[Desperate]

This is it.Thanks, appreciated.

 

[Desperate]

Dear stopher
Code works great. I just need a little more help from you. When there is no status mentioned in Table, OverallStatus of the qryResult is coming as "Partial". But if nothing is mentioned in status yet, overallStatus should also come as blank. I couldn't figure it out.Not to get any errors please define Column Headings in qryGroupByElement as "FULL","NONE"

 

[Desperate]

Solved!
IIf(nz([AVAILABLE],0)=0 And nz([NOT AVAILABLE],0)<>0,"NONE",IIf(nz([AVAILABLE],0)<>0 And nz([NOT AVAILABLE],0)=0,"FULL",IIf(nz([AVAILABLE],0)<>0 And nz([NOT AVAILABLE],0)<>0,"PARTIAL"," ")))