Subquery with Parameters

[Stephen]

I am trying to generate a query that does the following:

I have a database that lists my 1000 clients
Each client has retail location in various states in the United States
I am trying to create a form that would list the following:

1) a specific clientID would be passed to the query
2) the result set would always have 50 records; each state would be listed
as a separate record
3) Each of the 50 records would show the state name, and the number of
locations for the chosen client in that state.

Example:
Alaska 2
Arizona 1
Arkansas 0
California 3
Colorado 0
.....


Here are the tables I am using:
tblStates (StateID, StateName)
tblClientLocations (ClientID, StateID, LocationCity)
tblClients (ClientID, ClientName)

We are dealing with 1000's of clients, so we are trying to pass the query a
ClientID rather than getting a full list of records for all clients (50,000+
records) of which the form then filters the query.

I have considered using a union query (but couldn't get it to work) where I
union:
1) the list of states in which the client has locations
2) the list of states in which the client has no locations

Can anyone help with this query? I believe it is probably not that
complicated, but I am so deep in the weeds that I cannot look at it clearly.

NOTE: This is actually being built in SQL with views and in-line
fuunctions, but the query should translate from Access to SQL.

-Stephen

 

[Gerald Stanley]

Try something along the lines of

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
tbleClientLocations AS CL WHERE CL.clientId = pClientId)
ORDER BY stateName;

It is untested air-code so let me know if you have any
problems.

Hope This Helps
Gerald Stanley MCSD

 

[Stephen]

gerald -

This actually works! Thank you, however, how do I convert this to a SQL
query in an Access Project?

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

 

[John Spencer (MVP)]

Simplest query I can think of would be.

SELECT TblStates.StateName, Count(tblClientLocations.ClientID
FROM TblStates LEFT JOIN tblClientLocations
ON tblStates.StateID = TblClientLocations.StateID
WHERE tbkClientLocations.ClientID = [Specify Client ID]
GROUP BY TblStates.StateName

 

[John Spencer (MVP)]

Consarn it!!! I forgot the closing parens and the column name (alias)

SELECT TblStates.StateName,
Count(tblClientLocations.ClientID) as StoreCount
FROM TblStates LEFT JOIN tblClientLocations
ON tblStates.StateID = TblClientLocations.StateID
WHERE tbkClientLocations.ClientID = [Specify Client ID]
GROUP BY TblStates.StateName

Simplest query I can think of would be.

SELECT TblStates.StateName, Count(tblClientLocations.ClientID
FROM TblStates LEFT JOIN tblClientLocations
ON tblStates.StateID = TblClientLocations.StateID
WHERE tbkClientLocations.ClientID = [Specify Client ID]
GROUP BY TblStates.StateName

I am trying to generate a query that does the following:

I have a database that lists my 1000 clients
Each client has retail location in various states in the United States
I am trying to create a form that would list the following:

1) a specific clientID would be passed to the query
2) the result set would always have 50 records; each state would be listed
as a separate record
3) Each of the 50 records would show the state name, and the number of
locations for the chosen client in that state.

Example:
Alaska 2
Arizona 1
Arkansas 0
California 3
Colorado 0
....

Here are the tables I am using:
tblStates (StateID, StateName)
tblClientLocations (ClientID, StateID, LocationCity)
tblClients (ClientID, ClientName)

We are dealing with 1000's of clients, so we are trying to pass the query a
ClientID rather than getting a full list of records for all clients (50,000+
records) of which the form then filters the query.

I have considered using a union query (but couldn't get it to work) where I
union:
1) the list of states in which the client has locations
2) the list of states in which the client has no locations

Can anyone help with this query? I believe it is probably not that
complicated, but I am so deep in the weeds that I cannot look at it clearly.

NOTE: This is actually being built in SQL with views and in-line
fuunctions, but the query should translate from Access to SQL.

-Stephen

 

[Stephen]

John -

This cose will only show me the states that the clietn DOES have locations.
If the clietn only has locations in 10 states, then teh result set will only
have 10 records. I need th equery to return all 50 states (with a "0" next
to the states in which there are no locations for this particulate client.

Help?

-Stephen

Consarn it!!! I forgot the closing parens and the column name (alias)

SELECT TblStates.StateName,
Count(tblClientLocations.ClientID) as StoreCount
FROM TblStates LEFT JOIN tblClientLocations
ON tblStates.StateID = TblClientLocations.StateID
WHERE tbkClientLocations.ClientID = [Specify Client ID]
GROUP BY TblStates.StateName

Simplest query I can think of would be.

SELECT TblStates.StateName, Count(tblClientLocations.ClientID
FROM TblStates LEFT JOIN tblClientLocations
ON tblStates.StateID = TblClientLocations.StateID
WHERE tbkClientLocations.ClientID = [Specify Client ID]
GROUP BY TblStates.StateName

I am trying to generate a query that does the following:

I have a database that lists my 1000 clients
Each client has retail location in various states in the United States
I am trying to create a form that would list the following:

1) a specific clientID would be passed to the query
2) the result set would always have 50 records; each state would be listed
as a separate record
3) Each of the 50 records would show the state name, and the number of
locations for the chosen client in that state.

Example:
Alaska 2
Arizona 1
Arkansas 0
California 3
Colorado 0
....

Here are the tables I am using:
tblStates (StateID, StateName)
tblClientLocations (ClientID, StateID, LocationCity)
tblClients (ClientID, ClientName)

We are dealing with 1000's of clients, so we are trying to pass the query a
ClientID rather than getting a full list of records for all clients (50,000+
records) of which the form then filters the query.

I have considered using a union query (but couldn't get it to work) where I
union:
1) the list of states in which the client has locations
2) the list of states in which the client has no locations

Can anyone help with this query? I believe it is probably not that
complicated, but I am so deep in the weeds that I cannot look at it clearly.

NOTE: This is actually being built in SQL with views and in-line
fuunctions, but the query should translate from Access to SQL.

-Stephen

 

[Gerald Stanley]

The only change should be the parameter expression.

Try
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 = @ClientId
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 = @ClientId)
ORDER BY stateName

Hope This Helps
Gerald Stanley MCSD

 

[John Spencer (MVP)]

Yes, you are obviously correct. My error. I will need to ponder this further.

John -

This cose will only show me the states that the clietn DOES have locations.
If the clietn only has locations in 10 states, then teh result set will only
have 10 records. I need th equery to return all 50 states (with a "0" next
to the states in which there are no locations for this particulate client.

Help?

-Stephen

Consarn it!!! I forgot the closing parens and the column name (alias)

SELECT TblStates.StateName,
Count(tblClientLocations.ClientID) as StoreCount
FROM TblStates LEFT JOIN tblClientLocations
ON tblStates.StateID = TblClientLocations.StateID
WHERE tbkClientLocations.ClientID = [Specify Client ID]
GROUP BY TblStates.StateName

Simplest query I can think of would be.

SELECT TblStates.StateName, Count(tblClientLocations.ClientID
FROM TblStates LEFT JOIN tblClientLocations
ON tblStates.StateID = TblClientLocations.StateID
WHERE tbkClientLocations.ClientID = [Specify Client ID]
GROUP BY TblStates.StateName

Stephen wrote:

I am trying to generate a query that does the following:

I have a database that lists my 1000 clients
Each client has retail location in various states in the United States
I am trying to create a form that would list the following:

1) a specific clientID would be passed to the query
2) the result set would always have 50 records; each state would be listed
as a separate record
3) Each of the 50 records would show the state name, and the number of
locations for the chosen client in that state.

Example:
Alaska 2
Arizona 1
Arkansas 0
California 3
Colorado 0
....

Here are the tables I am using:
tblStates (StateID, StateName)
tblClientLocations (ClientID, StateID, LocationCity)
tblClients (ClientID, ClientName)

We are dealing with 1000's of clients, so we are trying to pass the query a
ClientID rather than getting a full list of records for all clients (50,000+
records) of which the form then filters the query.

I have considered using a union query (but couldn't get it to work) where I
union:
1) the list of states in which the client has locations
2) the list of states in which the client has no locations

Can anyone help with this query? I believe it is probably not that
complicated, but I am so deep in the weeds that I cannot look at it clearly.

NOTE: This is actually being built in SQL with views and in-line
fuunctions, but the query should translate from Access to SQL.

-Stephen

 

[Dale Fye]

How about:

PARAMETERS pClientId Number;
SELECT S.StateName
, Count(CL.ClientID) as LocCount
FROM TblStates S
LEFT JOIN (SELECT tblClientLocations.ClientID
, tblClientLocations.StateID
FROM tblClientLocations
WHERE tblClientLocations.ClientID = pClientID) as CL
ON S.StateID = CL.StateID
GROUP BY S.StateName

HTH
Dale

 

[Stephen]

Dale-

I have a code that works, but cannot get the correct syntax to have it run
in SQL (Access Project). Any thoughts on converting this (SQL won't accept
PARAMETERS)

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

 

[Dale Fye]

Stephen,

The problem with John's code is that the WHERE clause was limiting the
entire result set, not just what got returned from the ClientLocations
table. By creating the nested subquery, you limit the records that get
joined to the states table to those from the clients sites.

I think you'll find that my solution is significantly easier, and should be
faster as well, since it does not have to perform the operation of comparing
the various values on either side of the union query, and does not contain
the IN ( ) operation, which is inherently slow.

Having not used Access Projects, I'm not sure how you are interfacing with
SQL Server, but my guess is that this query is going to be a view or a
stored procedure. If it is a stored procedure, you can pass is the value
pClientID, and remove the Parameters line from this code.

Dale

 

[Gary Walter]

Hi Stephan,

I work "on the peripheral" of all this...
so this is what I think I know...

In SQL Server/MSDE I see a stored query as
taking on two forms:

- View ...a stored query w/o parameters
- Stored Procedure ..a stored query w/ parameters

I see you running the following SQL to create your stored
procedure:

CREATE PROC procClientStates
@pClientID int
AS
SET NOCOUNT ON
SELECT S.StateName
, Count(CL.ClientID) as LocCount
FROM TblStates S
LEFT JOIN (SELECT tblClientLocations.ClientID
, tblClientLocations.StateID
FROM tblClientLocations
WHERE
tblClientLocations.ClientID = @pClientID as CL
ON S.StateID = CL.StateID
GROUP BY S.StateName

On your form I see:

-- a combobox bound to "distinct ClientID's"
(say "cmboClientID")
(i.e., a query that returns all distinct ClientID's
and possibly client name, but its bound column
is to the ClientID field)

-- a listbox bound to
Exec procClientStates somevalidClientID
(say "lstClientStates")
(i.e., when you are first designing the form, you
might use "some valid ClientID", instead of referencing
the cmbo)

In the AfterUpdate event of the combobox,
I see your code testing that you have a valid ClientID,
then running these stmts:

Me.lstClientID.RowSource = "Exec procClientStates " & Me!cmboClientID
Me!lstClientID.Requery

Maybe that will help...

Gary Walter

 

[Gary Walter]

bad wording... should be:

- Stored Procedure ..a stored query that can accept parameters
(doesn't necessarily include parameters)