Distinct count problems

[douggy]

HI there,

I am running the following query in Access to get a distinct count. It produces the right results however when I try and save the query it crashes access. Can anyone see the problem with it.

Regards Mark

QUERY

SELECT Count(Client_ID) AS CountOfClientID FROM
(SELECT DISTINCT TblClassAttendance.Client_ID as
FROM TblClasses INNER JOIN TblClassAttendance ON TblClasses.ClassId = TblClassAttendance.ClassId
WHERE (((TblClasses.ClassDate) Between [start date] And [end date])));

 

[hermanlaksko]

Looks like NAME was missing. I doubt that its the whole story but it got it working here.

SELECT Count(Client_ID) AS CountOfClientID FROM
(SELECT DISTINCT TblClassAttendance.Client_ID as <b> NAME </B> FROM TblClasses INNER JOIN TblClassAttendance ON TblClasses.ClassId = TblClassAttendance.ClassId
WHERE (((TblClasses.ClassDate) Between [start date] And [end date])));

Herman
Say no to macros

 

[PHV]

Why not simply this ?
SELECT Count(*) AS CountOfClientID FROM
(SELECT DISTINCT A.Client_ID
FROM TblClasses AS C INNER JOIN TblClassAttendance AS A ON C.ClassId = A.ClassId
WHERE C.ClassDate Between [start date] And [end date]) AS D;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886