SQL Query translated to Access

[saschmeling]

Hi,

I am trying to translate this query to Access: The main challenge I am having is the Count column- it gives and error: Syntax Error(missing Operator) in Expression "Count....." some of it was already translated- but the count is where the issue is.

SELECT
TblFacility.StrFacilityName
, TblActivityGroup.GroupID
, TblActivityGroup.StrGroupName
, Count(TblClientActivities.ClientID) Over (Partition By tblFacility.StrFacilityName, TblActivityGroup.GroupID, TblActivityGroup.strGroupName Order By tblFacility.StrFacilityName, TblActivityGroup.GroupID) AS CountOfClientID
FROM
TblFacility
INNER JOIN
(TblActivityGroup
INNER JOIN
TblClientActivities
ON TblActivityGroup.GroupID = TblClientActivities.lngActivityGroup)
ON TblFacility.StrFacilityID = TblClientActivities.lngLocation
Where
(((TblClientActivities.DtmDate) Between Now() And DateAdd('m',-3,Now())));

Any help would be appreciated.

Thanks
Scott <!--IBF.ATTACHMENT_2478079-->

<!-- THE POST -->

 

[SydneyGeek]

Hi Scott, Access doesn't have a Partition function (at least not in this context).
Try...

SELECT
TblFacility.StrFacilityName
, TblActivityGroup.GroupID
, TblActivityGroup.StrGroupName
, Count(TblClientActivities.ClientID) AS CountOfClientID
FROM
TblFacility
INNER JOIN
(TblActivityGroup
INNER JOIN
TblClientActivities
ON TblActivityGroup.GroupID = TblClientActivities.lngActivityGroup)
ON TblFacility.StrFacilityID = TblClientActivities.lngLocation
Where
(((TblClientActivities.DtmDate) Between Now() And DateAdd('m',-3,Now())))
GROUP BY 
TblFacility.StrFacilityName
, TblActivityGroup.GroupID
, TblActivityGroup.StrGroupName;

Denis