Solved Summary Query of Yes/No Status (1 Viewer)

LGDGlen

Member
Local time
Today, 05:54
Joined
Jun 29, 2021
Messages
221
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:
1638798751342.png


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

1638798839499.png

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:

Code:
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

Super Moderator
Staff member
Local time
Today, 05:54
Joined
Feb 19, 2013
Messages
14,718
based on your query (I'll call it qry1) create a new query

Code:
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

Code:
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

Member
Local time
Today, 05:54
Joined
Jun 29, 2021
Messages
221
i think (think) i've figured it out

Code:
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;

1638800892719.png



1638800955364.png


1638800989618.png
 

Attachments

  • 1638800922952.png
    1638800922952.png
    4 KB · Views: 179

LGDGlen

Member
Local time
Today, 05:54
Joined
Jun 29, 2021
Messages
221
@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

Immoderate Moderator
Staff member
Local time
Yesterday, 23:54
Joined
Feb 28, 2001
Messages
23,175
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

Member
Local time
Today, 05:54
Joined
Jun 29, 2021
Messages
221
@The_Doc_Man yep that works:

Code:
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

Super Moderator
Staff member
Local time
Today, 05:54
Joined
Feb 19, 2013
Messages
14,718
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
 

Users who are viewing this thread

Top Bottom