if all subrecords is=* then show me records from main one.

[TipsyWolf]

hey guys,
i can't figure it out how to set a criteria for subtable if all related records will be with lets say status "completed"
2 example:

main table:
ID ------Isactive
1 --------true

sub table
ID--------status
1 ---- in progress
2 ---- completed

main table:
ID -----Isactive
2 ----- true

sub table
ID ------ status
3 ------completed
4 ------completed
5 ------completed

i need to get all records from main table where isactive= true (its simple), but as well as all record related to main table in this case ID2 have 3 out of 3 records from sub table with 100% completion. while ID1 has1 sub records with status "in progress'

picture will explain much better

 

[theDBguy]

In the criteria, just try entering "completed"

 

[TipsyWolf]

In the criteria, just try entering "completed"

yeah, but it shows ID2 three times ... but i need only 1

but what if RISK 2 has some subrecord with different status ?
i wont be able see in this case

i need to check like
IF every (100%) records are completed then show me the ID from main table.

so simple put it

main table - RISK table
sub table = Actions table. an actions need to perform in order to close RISK.

so i need to see if all actions have been performed. sometimes its 1 action , sometimes its 10 records.

 

[TipsyWolf]

In the criteria, just try entering "completed"

look

ID2

it doesnt fit to my criteris, as 1 sub records has in progress status.

i need to check if all subrecords "completed" in this case there are 4 records and its 3 out of 4 or 75%. completion
and if they are all = "completed" then give me ID from main table ... then i can do the rest ))

IF sub record ID 920 status would be = completed , then i want to see RiskID 2 in my queiry

 

[CJ_London]

Two queries

first one
Select distinct riskid from Tactions where status <>’completed’

second one

select tmain.* from tmain left join firstquery on main.riskid =firstquery.riskid where firstquery.riskid is null

 

[Gasman]

Why are you trying to redact status? If you click on image to see it better they are are redacted?
How on earth is that confidential?

Fortunately they are visible on a PC without having to click on the image, not so great on a phone.

 

[TipsyWolf]

If you click on image to see it better they are are redacted

didn't know it acts like it. its weird. just took paint to do it fast.
and its more about remove info that are not necessary in our case so ppl can read easily lol . this is absolutely non-confidential info.

 

[TipsyWolf]

made a screen shot for ppl using phones

 

[MajP]

This worked for me.

SELECT tmain.riskid,
       tmain.isactive,
       "All Actions Complete" AS AllComplete
FROM   tmain
WHERE  tmain.isactive = true
         AND EXISTS (SELECT *
                         FROM   tactions
                         WHERE  tactions.riskid = tmain.riskid
                                AND tactions.status <> "completed") = false

 

[TipsyWolf]

This worked for me.

SELECT tmain.riskid,
       tmain.isactive,
       "All Actions Complete" AS AllComplete
FROM   tmain
WHERE  tmain.isactive = true
         AND EXISTS (SELECT *
                         FROM   tactions
                         WHERE  tactions.riskid = tmain.riskid
                                AND tactions.status <> "completed") = false

u prob. will laugh at me , but looks like i set it up wrongly

 

[MajP]

You left in a comma before the "From" after isactive. I added a literal string that you took out.

 

[TipsyWolf]

This worked for me.

SELECT tmain.riskid,
       tmain.isactive,
       "All Actions Complete" AS AllComplete
FROM   tmain
WHERE  tmain.isactive = true
         AND EXISTS (SELECT *
                         FROM   tactions
                         WHERE  tactions.riskid = tmain.riskid
                                AND tactions.status <> "completed") = false

ID6 = all completed !

@MajP - thank u very much ! helped me a lot !