on click event based on 2 data sources

[TipsyWolf]

hello everyone. Im moving forward , just solved my issue with your help of course and i face with another issue i can't figure it out myself, so please help.
i have a 2 tables (one to many). 1 record (risk) has many actions.

i have a dashboard with my statistics

in countrol source of these i put
all =(DCount("riskcode";"Tmain";"riskcode >= 20 AND riskcode <=25"))
Completed =(DCount("riskcode";"Tmain";"riskcode >= 20 AND riskcode <=25 and isactive=true"))
In progress =(DCount("riskcode";"Tmain";"riskcode >= 20 AND riskcode <=25 and isactive=false"))
OverDue =(DCount("MinOfDueDate";"Qddall";"MinOfDueDate<date() and isactive=false and riskcode between 20 and 25"))

u prob. remember me asking how to save a calculated data to a table filed and u guys gave me adivce to never do it. so i tried to make a query instead with

and i have my amount of Risks where if 1 action of Risk (record) is overdue by today.


but i have also double click event on these

all =[riskcode] Between 20 And 25
Completed =[isactive]=True And [riskcode] Between 20 And 25
In progress =[isactive]=False And [riskcode] Between 20 And 25
OverDue =

but here .. OverDue is a headache for me , cause i need to link main Table AND query to put it in "where condition". if it would be one table , than its quite easy for me, but this is not my level...
how can i do this ?


or how can i tell WHERE condition it opens records on MasterList (countinius form - bound to Main Table) )what i have in my query.
in other words , i need Master List opens only records that fit to this condition

(DCount("MinOfDueDate";"Qddall";"MinOfDueDate<date() and isactive=false and riskcode between 20 and 25"))

 

[TipsyWolf]

i tried this one and it doesnt work

 

[TipsyWolf]

doesnt work as well

 

[TipsyWolf]

alright, let me put it straight and simple
how do i tell WHERE CONDITION to open only those Records in MasterList which shows query

here is ID 1, 2, 3 - how do i tell WHERE CONDITION open only those RiskIDs that in query.
i better do 5 query and 1 simple formula

 

[CJ_London]

Not having followed your earlier thread(s) I'm not familiar with what your data looks like or what you are trying to achieve. do you need the minofduedate field for display purposes? Why do you need it at all?

In future please post the sql rather than an image - makes it easier for us to help and consequently more likely to do so.

My best guess is you just need the count for each 'state' - all, completed, in progress and overdue.

SQL might look like

SELECT
TMain.RiskCode,
    Count(ActionID) AS All,
    -sum(DueDate<RiskDueDate AND isActive) as Completed,
    -sum(DueDate<RiskDueDate AND not isActive) as InProgress,
    -sum(DueDate<Date  AND not isActive) AS Overdue
FROM Tmain INNER JOIN Tactions ON Tmain.RiskID =Tactions.RiskID
WHERE Tmain.RiskCode between 20 and 25
GROUP BY Tmain.RiskCode

Not sure about your Overdue value, but that is what you have said it is.

note the calculations will return a boolean result > 0 for false and -1 for true, hence the use of -sum to turn a negative to a positive

 

[TipsyWolf]

Hi, @CJ_London , thank you for your reply !
should i put this code to query SQL ?
it says

do you need the minofduedate field for display purposes?

i have 2 tables. main and sub.
main - general Risk info. sub -actions that need to be done to close the Risk.

i need count every Risk where even 1 actions is overdue by today.
i did it with

=(DCount("MinOfDueDate";"Qddall";"MinOfDueDate<date() and isactive=false and riskcode between 20 and 25"))

but now i need to open a form (masterlist) where it opens only thoese records Risks, where Risks has more than 1 actions is overdue

 

[TipsyWolf]

just an example how it works
have a look at "2"

=(DCount("riskcode";"Tmain";"riskcode >= 20 AND riskcode <=25 and isactive=false"))

it gives me number of risks that are in progress.

i double click it
OpenForm
where Condition

[isactive]=False And [riskcode] Between 20 And 25

and i have only 2 records in my MasterList

but i dont know how to make it open risks where even more than 1 actions is overdue

 

[CJ_London]

with regards your query error - I provided aircode since you did not provide the sql for me to copy/paste so I may have made a typo - revisiting it looks like I should have used Date() rather than just Date if the error is to do with a reserved word.

 

[TipsyWolf]

with regards your query error - I provided aircode since you did not provide the sql for me to copy/paste so I may have made a typo - revisiting it looks like I should have used Date() rather than just Date if the error is to do with a reserved word.

changed it. it highlights ALL

SELECT
TMain.RiskCode,
    Count(ActionID) AS All,
    -sum(DueDate<RiskDueDate AND isActive) as Completed,
    -sum(DueDate<RiskDueDate AND not isActive) as InProgress,
    -sum(DueDate<Date()  AND not isActive) AS Overdue
FROM Tmain INNER JOIN Tactions ON Tmain.RiskID =Tactions.RiskID
WHERE Tmain.RiskCode between 20 and 25
GROUP BY Tmain.RiskCode

 

[CJ_London]

All is a reserved word - change it to something else such as AllRisks

see this link about reserved words

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

learn.microsoft.com

 

[TipsyWolf]

In future please post the sql rather than an image

SELECT Tmain.RiskID, Tmain.isActive, Tmain.RiskCode, Min(Tactions.DueDate) AS MinOfDueDate
FROM Tmain INNER JOIN Tactions ON Tmain.RiskID = Tactions.RiskID
GROUP BY Tmain.RiskID, Tmain.isActive, Tmain.RiskCode;

i use this query to get my numbers of specific condition

 

[TipsyWolf]

All

but i have nothing with ALL. don't I ?