Help with counting

Question

I am trying to count procedures done by each doctor(PrimarySurgeon) within a specified range entered by a prompt. There is only one table. the SQL below gives "Your query does not include the specified expression 'PrimarySurgeon' as part of an aggregate function" when executed.

SELECT UrologyTable.PrimarySurgeon, UrologyTable.[Count(*)] AS CaseCount FROM UrologyTable WHERE (((UrologyTable.SurgeryDate) Between [Enter Start Date (MM/DD/YYYY)] And [Enter End Date (MM/DD/YYYY)])) GROUP BY UrologyTable.PrimarySurgery;

Answer 1

I would make two recommendations:

1.  With columns of DateTime data type declare the parameters.  This avoids the value entered being inadvertently treated as an arithmetical expression, and returning the wrong results.

2.  Define the date range as on or later than the start date, and less than the date following the end date.  This allows for values on the final day of the range which might contain a non-zero time of day element.  Unless you have made specific provision in the table definition to disallow such values you cannot discount the possibility of there being such values in the table.

So, the query would then be:

PARAMETERS [Enter Start Date (MM/DD/YYYY)] DATETIME,

    [Enter End Date (MM/DD/YYYY)] DATETIME;

SELECT PrimarySurgeon, COUNT(*) AS CaseCount

FROM UrologyTable

WHERE SurgeryDate >= [Enter Start Date (MM/DD/YYYY)]

    AND  SurgeryDate < [Enter End Date (MM/DD/YYYY)] + 1

GROUP BY PrimarySurgeon;

Answer 2

When I run this, it is prompting me to enter an unknown information before showing the date prompts.

Answer 3

this worked, thanks.

Answer 4

SELECT urologytable.primarysurgeon,
       urologytable.[count(*)] AS CaseCount
FROM   urologytable
WHERE  (( ( urologytable.surgerydate ) BETWEEN
                  [enter start date (mm/dd/yyyy)] AND
                  [enter end date (mm/dd/yyyy)] ))
GROUP  BY urologytable.primarysurgery; 

Spot the difference?

Answer 5

Is the field in the GROUP BY clause supposed to be PrimarySurgery or PrimarySurgeon?