Solved Summary Query of Yes/No Status

[LGDGlen]

Hi All

I have sets of records that all have a reference number and a status that i would like to query as follows:

For example:

This would show 1 line for M00158 with a consolidated status of No as not all the records statuses are Yes

This would show 1 line for HLBU9503742 with a consolidated status of Yes as they are all Yes

So basically if any record has a field with No the summary will be No, otherwise it will be Yes

I know this almost certainly a very simple thing to do but i'm not having a good day so unable to think it through so would appreciate just a little bit of help nudging me in the right direction.

The sql for the query i've got so far is:

SELECT [FACT-Consignments].cons_albaran, [FACT-DeliveryProducts].del_prod_return_received
FROM [FACT-Consignments] INNER JOIN ([FACT-Deliveries] INNER JOIN [FACT-DeliveryProducts] ON [FACT-Deliveries].del_id = [FACT-DeliveryProducts].del_prod_del_id) ON [FACT-Consignments].cons_id = [FACT-Deliveries].del_cons_id;

And i'm sure its a matter of just modifying it slightly and i'll feel a little bit of a fool when i see it but just can't see what to do at the moment

thanks in advance

Glen

 

[CJ_London]

based on your query (I'll call it qry1) create a new query

SELECT DISTINCT cons_albaran
FROM qry1
WHERE del_prod_return_received=false-- or use 'No' if del_prod_return_received is a text field

save that as qry2

now create a third query

SELECT DISTINCT qry1.cons_albaran, iif(qry2.cons_albaran is null, true, false) as Status ---- or 'yes','no' if del_prod_return_received is a text field
FROM qry1 LEFT JOIN qry2 ON qry1.cons_albaran=qry2.cons_albaran

 

[LGDGlen]

i think (think) i've figured it out

SELECT [FACT-Consignments].cons_albaran, Min([FACT-DeliveryProducts].del_prod_return_received) AS MinOfdel_prod_return_received
FROM [FACT-Consignments] INNER JOIN ([FACT-Deliveries] INNER JOIN [FACT-DeliveryProducts] ON [FACT-Deliveries].del_id = [FACT-DeliveryProducts].del_prod_del_id) ON [FACT-Consignments].cons_id = [FACT-Deliveries].del_cons_id
GROUP BY [FACT-Consignments].cons_albaran
HAVING (((Min([FACT-DeliveryProducts].del_prod_return_received)) Is Not Null))
ORDER BY [FACT-Consignments].cons_albaran;

 

[LGDGlen]

@CJ_London thank you, i'll take a look at your solution because honestly i was just messing about with things and stumbled upon something that appears to work but might not be the best way of doing it.

thank you for your message

 

[The_Doc_Man]

If you have not "diddled" with the Y/N field, then YES will be represented by -1 and NO will be represented by 0. Take SUM( NOT DEL_PROD_RETURN_RECEIVED ) which will be 0 ( = "NO" ) if and only if all of those fields were YES. Otherwise it will be non-zero, which will be interpreted as a YES.

 

[LGDGlen]

@The_Doc_Man yep that works:

SELECT [FACT-Consignments].cons_albaran, Sum(Not [del_prod_return_received]) AS Expr1
FROM [FACT-Consignments] INNER JOIN ([FACT-Deliveries] INNER JOIN [FACT-DeliveryProducts] ON [FACT-Deliveries].del_id = [FACT-DeliveryProducts].del_prod_del_id) ON [FACT-Consignments].cons_id = [FACT-Deliveries].del_cons_id
GROUP BY [FACT-Consignments].cons_albaran
ORDER BY [FACT-Consignments].cons_albaran;

@CJ_London was going to try your way but if i can do it in 1 query it seems a simpler method so i'm gonna stick with @The_Doc_Man's method but once again all thank you for your help

 

[CJ_London]

does potentially look like a solution

only thing I would say is with booleans, 0 is false, anything else is true so if you have two records where Not [del_prod_return_received] is true, you will get -2 - i.e. true. So I would check that situation

an alternative
abs(sum([del_prod_return_received]))=count([del_prod_return_received]) AS Expr1

 

[LGDGlen]

@CJ_London thank you for following up, i'll take what you've stated and run a few tests