-
I'm :confused: ,
I'm making a DB app to an hospital and there is a question that is intrigging me.
I've to make a report (currently using Crystal RDC 6.0) with the amount of exams (severall kinds of exams) made by a department. The DB only gives me the exams by patient so I've to make an SQL like:
SQL = "Select * from tableX WHERE exam = '" & examKind & "' AND department = '" & departmentName & "'"
There are many kinds of exams so I need to do serveral re-calls of that SQL (it takes me to long. Is there an easy way ? I've tried:
SQL = "SELECT * from tablex WHERE department = '" & departmentName & "'"
and from there count record by record (rs.movenext) the kind of exam and add it to an array. TOO SLOW ...
Is there a simpler way ?
I know that every time in the past something like this appened to me there was allways a simpler way...
Thank in advance,
------------------
Jorge Ledo
[email protected]
Portugal
-
Why don't you Group the Results by Exam and use the COUNT() Function, then you can easily return the Exam Name and How many Times a Specified Department Took that Exam very easily, ie.
SQL = "SELECT Exam, COUNT(Exam) As [Times Taken] FROM TableX WHERE Department = '" & sDepartment & "' GROUP BY Exam"
------------------
Aaron Young
Analyst Programmer
[email protected]
[email protected]
-
It turned out as a much complex task.
It was solved by this SQL statment:
sql = "SELECT inscricoes.nr_inscricao, inscricoes_exames.cod_inscricao, inscricoes_exames.cod_exame, inscricoes.cod_entidade, utentes.cod_posto_clinico, inscricoes.data
FROM postos_clinicos INNER JOIN (utentes INNER JOIN (inscricoes INNER JOIN inscricoes_exames ON inscricoes.nr_inscricao = inscricoes_exames.cod_inscricao) ON utentes.codigo = inscricoes.cod_utente) ON postos_clinicos.codigo = utentes.cod_posto_clinico
WHERE (((inscricoes_exames.cod_exame)=" & ExamType & ") AND ((utentes.cod_posto_clinico)= " & DepartmentName & ") AND ((inscricoes.data)> " & BeginDate & " And (inscricoes.data)< " & EndDate & "));"
But thank you very much Aaron your reply helped me to understand the COUNT statment.
Thank you,
------------------
Jorge Ledo
[email protected]
Portugal
