Multiple counts ... (DBase's)

[JorgeLedo]

I'm ,

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

[Aaron Young]

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]

[JorgeLedo]

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