Convert to SQL Query

S

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
 
N

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top