Query doesn't count with criteria.

[amaro.neto]

Thank you very much for your help, you're a good person Orange, thank you. That is exactly what I need. I'll try to study more database relationships to make better DBs to improve our reports. But, for now, I'll work on the solution that you provided here.
Thank you!

[orange]

I have done some additional testing this morning (fresh start).
I devised the following SQL to identify the PercentS of each Bug/Bicho * ATB combination. This does not require the separate table. Try this SQL on your existing database. My testing was all done with your latest posted database.

The SQL for query Q_PercentSForEachBugATBCombo

SELECT Bicho.NomeBicho
    ,ATB.Nome
    ,Sum([Union_Alias].R_count) AS RNum
    ,Sum([Union_Alias].S_count) AS SNum
    ,Round(Snum / (Rnum + SNum) * 100, 2) AS PercentS
FROM (
    (
        SELECT TesteATB.ATBId
            ,TesteBicho.BichoId
            ,TesteATB.SensId AS ResR
            ,Count(TesteATB.TesteATBId) AS R_Count
            ,0 AS S_count
        FROM TesteBicho
        INNER JOIN TesteATB ON TesteBicho.TesteBichoId = TesteATB.TesteBichoId
        WHERE (((TesteATB.[sensid]) = "R"))
        GROUP BY TesteATB.ATBId
            ,TesteBicho.BichoId
            ,TesteATB.SensId
        
        UNION
        
        SELECT TesteATB.ATBId
            ,TesteBicho.BichoId
            ,TesteATB.SensId AS ResS
            ,0 AS R_count
            ,Count(TesteATB.TesteATBId) AS S_Count
        FROM TesteBicho
        INNER JOIN TesteATB ON TesteBicho.TesteBichoId = TesteATB.TesteBichoId
        WHERE (((TesteATB.[sensid]) = "S"))
        GROUP BY TesteATB.ATBId
            ,TesteBicho.BichoId
            ,TesteATB.SensId
        ) AS Union_Alias INNER JOIN Bicho ON [Union_Alias].BichoId = Bicho.BichoId
    )
INNER JOIN ATB ON [Union_Alias].ATBId = ATB.ATBId
GROUP BY Bicho.NomeBicho
    ,ATB.Nome;

Here is part of the output from this query.




Good luck.

[amaro.neto]

Wow, that worked brilliantly! You're the man!
I'll study that code to understand what you did. But it is working very well. Thank you!

[orange]

Happy to help. Good luck with your project.