Big SQL Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

SELECT Format([Accept_Time],"yyyy") AS [Year], Format([Accept_Time],"mm") AS [Month], Format([Accept_Time],"dd") AS [Day], Format([Accept_Time],"ddd") AS [Long Day], Format([Accept_Time],"hh") AS [Hour], a.QUEUE, e.WORK_GROUP, e.LOCATION, Count(DISTINCT e.LOGIN_NAME) AS [Head Count
FROM [Local DispatchAccept] AS a INNER JOIN [Local Employee] AS e ON a.ACCEPT_LOGIN_NAME = e.LOGIN_NAM
GROUP BY Format([Accept_Time],"yyyy"), Format([Accept_Time],"mm"), Format([Accept_Time],"dd"), Format([Accept_Time],"ddd"), Format([Accept_Time],"hh"), a.QUEUE, e.WORK_GROUP, e.LOCATION

i am trying to execute this SQL statement in Acces
Unfortunatly there is an error on Count(Distinct e.LOGIN_NAME) in the form o
Syntax Error (Missing Operator) in Query Expression can anyone help with i
Thanking you in advanc
Guest
 
Access does not appear to support the Count(Distinct
columnName) syntax. I've had to select distinct then count
from there.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
SELECT Format([Accept_Time],"yyyy") AS [Year],
Click to expand...
Format([Accept_Time],"mm") AS [Month],
Format([Accept_Time],"dd") AS [Day],
Format([Accept_Time],"ddd") AS [Long Day],
Format([Accept_Time],"hh") AS [Hour], a.QUEUE,
e.WORK_GROUP, e.LOCATION, Count(DISTINCT e.LOGIN_NAME) AS
[Head Count]
FROM [Local DispatchAccept] AS a INNER JOIN [Local
Click to expand...
Employee] AS e ON a.ACCEPT_LOGIN_NAME = e.LOGIN_NAME
GROUP BY Format([Accept_Time],"yyyy"),
Click to expand...
Format([Accept_Time],"mm"), Format([Accept_Time],"dd"),
Format([Accept_Time],"ddd"), Format([Accept_Time],"hh"),
a.QUEUE, e.WORK_GROUP, e.LOCATION;
 
SELECT [Year] ,[Month], [Day], [Long Day], [Hour], [Queue],
[WorkGroup],[Location], Count[HeadCount] FROM
(SELECT DISTINCT Format([Accept_Time],"yyyy") AS [Year],
Format([Accept_Time],"mm") AS [Month],
Format([Accept_Time],"dd") AS [Day],
Format([Accept_Time],"ddd") AS [Long Day],
Format([Accept_Time],"hh") AS [Hour], a.QUEUE,
e.WORK_GROUP, e.LOCATION, e.LOGIN_NAME AS [Head Count]
FROM [Local DispatchAccept] AS a INNER JOIN [Local
Employee] AS e ON a.ACCEPT_LOGIN_NAME = e.LOGIN_NAME)
GROUP BY [Year] ,[Month], [Day], [Long Day],[Hour],
[Queue], [WorkGroup],[Location];

Itis untested air-code but should show the principle.

Hope This Helps
Gerald Stanley MCSD
 
Back
Top