Convert to SQL Query

[Stephen]

Does anyone know the syntax to convert the following MS Access MDB query to
an Access Project SQL view/function?

PARAMETERS pClientId Number;
SELECT S.stateName, Count(CL.locationCity) AS locationCount
FROM tblStates AS S INNER JOIN tblClientLocations AS CL ON
S.stateId = CL.stateId
WHERE CL.clientId = pClientId
GROUP BY S.stateName
UNION
SELECT S.stateName, 0
FROM tblStates AS S
WHERE S.stateId NOT IN (SELECT DISTINCT stateId FROM
tblClientLocations AS CL WHERE CL.clientId = pClientId)
ORDER BY stateName

-Stephen

 

[Norman Yuan]

What yu need is Stored Procedure, instead of View, in SQL Server/MSDE
(Stored Procedure/View is saved in SQL Server, not in Access Project file),
because View in Sql Server cannot take parameter.

The stored procedure in SQL Server would be almost identical to your
existing Access query: you only need to replace "PARAMETER..." with "CREATE
....( param) AS...", like following,

Create Procedure "MyQuery"
(
@pClientID int
)
As
SELECT S.stateName, Count(CL.locationCity) AS locationCount
FROM tblStates AS S INNER JOIN tblClientLocations AS CL ON
S.stateId = CL.stateId
WHERE CL.clientId = pClientId
GROUP BY S.stateName
UNION
SELECT S.stateName, 0
FROM tblStates AS S
WHERE S.stateId NOT IN (SELECT DISTINCT stateId FROM
tblClientLocations AS CL WHERE CL.clientId = pClientId)
ORDER BY stateName