Problem with DISTINCT Count

[kate10123]

Hi All,

I am trying to count the number of different students seen between two dates.

I have tried the following:

SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708)  AS T
WHERE (((tbl_TutorialRecords0708.Date) Between #9/1/2007# And #8/31/2008#));

this statement works fine without a date range clause in it but I really need to only count the number of students between these particular dates.

Is there anything wrong with my syntax?

When I run the above statement it brings up a parameter box saying 'tbl_TutorialRecords.Date' and then a space to enter.

Any ideas appreciated

 

[Brianwarnock]

Try

SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708 WHERE tbl_TutorialRecords0708.Date) Between #9/1/2007# And #8/31/2008#)  AS T
;

The date range is applied in the subquery

Brian

 

[kate10123]

Hi this didn't seem to work.

Went to run the query with this code and it said there was a syntax error.

Any other ideas?

 

[MSAccessRookie]

Hi All,

I am trying to count the number of different students seen between two dates.

I have tried the following:

SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708)  AS T
WHERE (((tbl_TutorialRecords0708.Date) Between #9/1/2007# And #8/31/2008#));

this statement works fine without a date range clause in it but I really need to only count the number of students between these particular dates.

Is there anything wrong with my syntax?

When I run the above statement it brings up a parameter box saying 'tbl_TutorialRecords.Date' and then a space to enter.

Any ideas appreciated

I offer two suggestions:

SELECT Count(*) AS Students 
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708)  AS T
WHERE (T.Date Between #9/1/2007# And #8/31/2008#);

AND

SELECT Count(*) AS Students FROM
    (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708  AS T
     WHERE tbl_TutorialRecords0708.Date Between #9/1/2007# And #8/31/2008#);

• The first is based on the fact that the only "Table" referred to is the Table Alias "T" that is assigned to the query.
• The Second is a correction to the syntax of the option provided by Brian

Either one should work.

 

[Brianwarnock]

OOPs! had a stray ) after tbl_TutorialRecords0708.Date, I would have thought that that would have been highlighted with the syntax error?

SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708 WHERE tbl_TutorialRecords0708.Date Between #9/1/2007# And #8/31/2008#)  AS T
;

Brian

 

[MSAccessRookie]

OOPs! had a stray ) after tbl_TutorialRecords0708.Date, I would have thought that that would have been highlighted with the syntax error?

SELECT Count(*) AS Students
FROM (SELECT DISTINCT StudentID FROM tbl_TutorialRecords0708 WHERE tbl_TutorialRecords0708.Date Between #9/1/2007# And #8/31/2008#)  AS T
;

Brian

All of us have the right to not be perfect all of the time. In fact I am sure that I have done worse than that. The important thing is that your option as modified is as good as mine.