Query doesn't count with criteria.

[amaro.neto]

Hello, guys.
I've made a query that I thought it would count the "Sensível" Records. But I don't know what it is returning. The query is called "Grambicho Consulta" and its column is Qtd.
I just want to count the Sensível records for each ATB Nome and NomeBicho, because I want to calculate the rate of "Sensivel" per ATB Nome and NomeBicho. I would be grateful for any help.
BDPerfilOficial.zip

[ranman256]

you may be complicating the query with the IIF,
instead , make a simpler query, Q1, to pull data with "Sensivel" in CRITERIA.

then Q2, use Q1 to count.

[amaro.neto]

Thanks for the answer, I can count "Sensível" but I just can't calculate the rate now. Any Ideas about it?
I want to know the rate of "Sensível" in the [ATB].[Nome] and [Bicho].[NomeBicho] columns...

[June7]

What you are asking doesn't really make sense. Sensivel occurs same number of times in relation to Nome and NomeBicho in that query. Quite possibly can't get what you want in one query. Perhaps if you provide a mockup table of the expected output, we could try to achieve that result. Meantime, consider:

SELECT Bicho.NomeBicho, Count(*)/DCount("*","TesteATB","Sensid='S'")*100 AS PctSens
FROM (Bicho INNER JOIN TesteBicho ON Bicho.[BichoId] = TesteBicho.[BichoId]) INNER JOIN TesteATB ON TesteBicho.[TesteBichoId] = TesteATB.[TesteBichoId]
WHERE (((TesteATB.SensId)="S"))
GROUP BY Bicho.NomeBicho;

SELECT ATB.Nome, Count(*)/DCount("*","TesteATB","Sensid='S'")*100 AS PctSens
FROM ATB INNER JOIN TesteATB ON ATB.[ATBId] = TesteATB.[ATBId]
WHERE (((TesteATB.SensId)="S"))
GROUP BY ATB.Nome;

[amaro.neto]

Thanks for the answer.
Well, actually what I want is to know how much a BichoNome is "sensível" to [ATB].[Nome]. Like Staphylococcus aureus is 0% sensível to Oxacilina and 100% sensível to Eritromicina.
[Sensibilidade] can receive 2 values: Sensível or Resistente. in that case all Oxacilina for Staphylococcus aureus received Resistente in Sensibilidade Field. But, if I add a record that Sensibilidade Field could be Sensível, the rate should be Staphylococcus aureus is 33% sensível to Oxacilina.
Thanks for the help guys.

[amaro.neto]

I've tried another strategy:
In the query, I've put on a column, called Conta, the Count(*) statement, and in another, called Soma, I've put DSum("[Conta]"; "Grambicho Consulta";"[Nome]='"&[Nome]&"' And [NomeBicho]='"&[NomeBicho]&"'"), thinking that I could divide Conta over Soma to have the rate, but access gives me the error: "Cannot open anymore tables".
=/.
Is there anything that I can do to overcome this?

[orange]

Well, actually what I want is to know how much a BichoNome is "sensível" to [ATB].[Nome]. Like Staphylococcus aureus is 0% sensível to Oxacilina and 100% sensível to Eritromicina.
[Sensibilidade] can receive 2 values: Sensível or Resistente. in that case all Oxacilina for Staphylococcus aureus received Resistente in Sensibilidade Field. But, if I add a record that Sensibilidade Field could be Sensível, the rate should be Staphylococcus aureus is 33% sensível to Oxacilina.

I'm trying to work through your requirement.
BichoNome is NomeBicho in your table, correct?

Do you have a query that isolates the data in your quote? That is, can we start with a simple query that deals with SAureus and 1 or 2 ATBs, then build on that??

[amaro.neto]

Thanks for the reply.
I mistyped the name of the field, sorry, and yes it is actually NomeBicho.
I don't have that query, because I'm interested to know the rate of "Sensível" of each NomeBicho over each ATB. But I think it would be a very good start knowing how to do it with less number of Bicho and ATBs.
Actually I don't know why I couldn't sum the values of Resistente and Sensível from a column with the Count(*) statement, I think it would solve the problem, because I could just divide the count(*) by the sum.
Thanks again

[orange]

Try that and let us know. Remember we are not familiar with your environment nor the tables and tests.
It seems you are determining some sort of significance of an ATB on a Bicho based on some tests.
And if you can get it working with some small volume of data, you can increase that volume to get to the query you need.

[amaro.neto]

It worked with only one Bicho! Look at the ConsultaTaxa query:
BDPerfilOficial (2).zip
But it is weird, because when I change the NomeBicho criteria, nothing appears on the Total and Taxa columns =O

[orange]

Where are you with this at the moment?
Perhaps there is a way to use subquery or totals query and eliminate the DSUM,
or a vba solution to iterate over the ATB and/or Bicho.
and
Have you looked/tested the options that June provided in post#4?

Do you have a 1 line clear statement of what you are trying to accomplish?
Perhaps put this into other terminology> I'm interested to know the rate of "Sensível" of each NomeBicho over each ATB.

Can you work from this (if it is correct)

To identify the number of tests for each Bicho and ATB where the Sensitivity is S and the Sensitivity is R, then show
Count S/Count(R+S) as a percent?

[amaro.neto]

Well, I'm really frustrated because this last query should solve everything, but when I change the criteria, the Total and Taxa columns appears empty, it is only working for the first criteria I've puted on NomeBicho.
I've tried those queries that Juno wrote, it gives the total rate of sensível from each BichoNome, and the other the rate of sensível for each ATB.Nome, and I don't know how to combine them to give what I want.
I wrote about that in other post some years ago, and you gave the basic model for the DB. I've implemented it last month, and now I'm stucked with that rate.
Actually, we test the sample (table: Amostra) to know what microrganism will appear (TesteBicho table that have a relation with table Bicho) and then we test the microrganism for his sensitivity against antibiotics (TesteATB table that has a relation with ATB table and Suscetibilidade table), at the end of the day, to know the rate of sensitivity, we need to know how many tests were made with that antibiotics and how many were sensible (Sensível). I think that's it.
Thanks again!

[orange]

I wrote about that in other post some years ago, and you gave the basic model for the DB.

Really? Do you have a link to that? Found it.

Wow! Feb 24, 2017...I had forgotten this. Model was done with Erwin who dropped the free copy back in 2017 ---2 years ago..
related thread for anyone viewing https://www.accessforums.net/showthread.php?t=64538

I think the big issue at the moment is the Portuguese/English.

amostra         sample
bicho             bug
infecção         infection
paciente         patient
material         material
sensibilidade  sensitivity
setor             sector
topografia      topography

So, there are a number of tests in which the bug/Bicho is "tested" with antibiotic ATB, and the requirement is to see how often the
ATB vs the Bicho is Sensitive (reduces/removes Bicho activity).

A small test gave this result. What should be reported??

NomeBicho	Nome	SensId	DataTesteATB
Staphylococcus aureus	Eritromicina	R	20-Oct-18
Staphylococcus aureus	Eritromicina	S	20-Oct-18
Staphylococcus aureus	Eritromicina	S	23-Oct-18

Another test. What should be reported? Please show what you want from this.

NomeBicho	Nome	SensId	DataTesteATB
Staphylococcus aureus	Oxacilina	R	23-Oct-18
Staphylococcus aureus	Oxacilina	R	20-Oct-18
Staphylococcus aureus	Oxacilina	R	20-Oct-18
Staphylococcus aureus	Oxacilina	R	20-Oct-18

[amaro.neto]

Nice, from those queries I want something like this

NomeBicho	Nome	%S
Staphylococcus aureus	Oxacilina	0%
	Eritromicina	66,6%

I won't be able to test that until monday.
Thank you very much for your help!

[orange]

My understanding of the requirement is not entirely clear. In my review while working with your code, it seems the expression required to get the rate or Percent Sensitive or Resistant of each Bug/Bicho vs ATB based on test results was/is

%S of Bug X with ATB Y = Number of S results/(Number S Results +Number of R results) * 100 and
%R of Bug X with ATB Y = Number of R results/(Number S Results +Number of R results) * 100

I did not have any success to get the final result with SQL queries. I could get pieces, as I showed in my previous post, but could not get all the pieces for all the Bug/ATB combinations and the proper count of test Results. Somebody else more familiar with SQL queries may be able to avoid the table and get the solution directly.

I decided to identify a simple format that would put the necessary pieces together. I had no luck with a variety of queries so I decided to:

=create a table (JackTestCopy) of the basic need with fields for: 
--BugID   
--ATBID
--result    -----your sensibilidade
--resultR  -----number of R results
--resultS  -----number of S results
--NumTests

and populate this table with a few queries of your existing data. I did identify those Bug/ATM combinations where test results were partly S and partly R.

SELECT jacktest.ATBId, jacktest.BichoId, count(*) AS cnt
FROM (SELECT jacktest.ATBId, jacktest.BichoId, jacktest.RESULT, Sum(jacktest.numtests) AS TotTest FROM jacktest GROUP BY jacktest.ATBId, jacktest.BichoId, jacktest.RESULT)  AS [%$##@_Alias]
GROUP BY jacktest.ATBId, jacktest.BichoId
HAVING count(*) >1;

These were:

ATBId	BichoId	cnt
ERI	SAUREUS	2
ERI	SEPIDERM	2
ERI	SHAEMOL	2
OXA	SEPIDERM	2

Here is a graphic showing some of the data in table JackTestCopy.



By getting the R and S numbers for each Bug/ATB combination into a single table (or query), the %S and %R are easily derived with a query.

Here is my query against the populated table (you could get the true names of Bicho and ATB and/or other info by adding the relevant tables to this query)

SELECT JackTestCOPY.ATBId
, JackTestCOPY.BichoId
, JackTestCOPY.resultR
, JackTestCOPY.resultS
, JackTestCOPY.NumTests
, Round([resultR]/[numtests]*100,2) AS PctR
, Round([resultS]/[Numtests]*100,2) AS PctS
FROM JackTestCOPY;

Here is a graphic of part of that query output.


Please note that the draft model I provided in Feb 2017 was intended as a DRAFT-- a starting point to be tested and adjusted as needed.
Por favor, note que o modelo preliminar que eu forneci em fevereiro de 2017 foi planejado como um ponto de partida para ser testado e ajustado necessário.

It's unfortunate that there was a long delay before working with that model. The software that I created that model with is no longer available as a free/community edition. I have removed that software and models created with it from my system. I do keep jpg/png of some models.

You may find the links and materials re Database Planning and Design at this page useful.

Good luck with your project.