Subqueries

[Scott]

I am getting started with subqueries, working to create a report on
leadership data. Can you give me some advice as I try to blend three
queries into one?

Here are the three queries:

--Count of the unique leadership positions at the company:
SELECT Count([t_Eng Leadership Master].[Leader Pernr]) AS [# of
Positions]
FROM (SELECT DISTINCT [Leader Pernr] FROM [t_Eng Leadership Master])
AS [Leadership Position Data];

--The count of unique leader postions with no backup
SELECT Count([t_Eng Leadership Master].[Leader Name]) AS [# of Leader
Positions with no planned back-up]
FROM (SELECT DISTINCT [Leader Name] FROM [t_Eng Leadership Master]
WHERE [Candidate Name] IS NULL) AS [Leadership Position Data];

--The count of unique leader positions with no backup rated as ready
"Now"
SELECT Count([t_Eng Leadership Master].[Leader Name]) AS [# of Leader
Positions with no planned back-up]
FROM (SELECT DISTINCT [Leader Name] FROM [t_Eng Leadership Master]
WHERE [Readiness] <> "Now") AS [Leadership Position Data];

Can you show me how to blend these into one query?

Thanks!

Scott

 

[arthurjr07]

I assume you want to display the
data in seperate column.


SELECT
A.[#ofPositions],
B.[# of Leader Positions with no planned back-up],
C. [# of Leader Positions with no planned back-up]
FROM
(SELECT Count([t_Eng Leadership Master].[Leader Pernr]) AS [# of
Positions]
FROM (SELECT DISTINCT [Leader Pernr] FROM [t_Eng Leadership Master])
AS [Leadership Position Data]) AS A,
(SELECT Count([t_Eng Leadership Master].[Leader Name]) AS [# of Leader
Positions with no planned back-up]
FROM (SELECT DISTINCT [Leader Name] FROM [t_Eng Leadership Master]
WHERE [Candidate Name] IS NULL) AS [Leadership Position Data]) AS B,
(SELECT Count([t_Eng Leadership Master].[Leader Name]) AS [# of Leader
Positions with no planned back-up]
FROM (SELECT DISTINCT [Leader Name] FROM [t_Eng Leadership Master]
WHERE [Readiness] <> "Now") AS [Leadership Position Data]) AS C

 

[Scott]

Thanks for the help. That worked well. Can you suggest what I could do
to add columns for percentages? I've tried to figure this out today and
have had no luck.

I need to add two additional columns:
D.[Percent_without_back-up]
E.[Percent_without_ready_back-up]

Below is the query you sent with a couple of tweaks... this runs well
(thank to your suggestion from yesterday).

SELECT
A.[Leader_Positions],
B.[Positions_without_back-up],
C.[Positions_without_ready_back-up]
FROM
(SELECT Count([t_Eng Leadership Master].[Leader Name]) AS
[Leader_Positions]
FROM (SELECT DISTINCT [Leader Name] FROM [t_Eng Leadership Master])
AS [Leadership Position Data]) AS A,
(SELECT Count([t_Eng Leadership Master].[Leader Name]) AS
[Positions_without_back-up]
FROM (SELECT DISTINCT [Leader Name] FROM [t_Eng Leadership Master]
WHERE [Candidate Name] IS NULL) AS [Leadership Position Data]) AS B,
(SELECT Count([t_Eng Leadership Master].[Leader Name]) AS
[Positions_without_ready_back-up]
FROM (SELECT DISTINCT [Leader Name] FROM [t_Eng Leadership Master]
WHERE [Readiness] <> "Now") AS [Leadership Position Data]) AS C;

Can you help me understand how to add subqueries to calculate the
percentages?

Thanks again,

Scott

 

[arthurjr07]

I dont think you need another subquery to get the percentage.

Since A.[Leader_Positions] returns the count of (t_Eng Leadership
Master) table
you can divide B.[Positions_without_back-up] with A.[Leader_Positions]
and
C.[Positions_without_ready_back-up] with A.[Leader_Positions]

Like this.I hope my assumption is correct.


SELECT
A.[Leader_Positions],
B.[Positions_without_back-up],
C.[Positions_without_ready_back-up],
(B.[Positions_without_back-up]/A.[Leader_Positions])*100 AS
[Percent_without_back-up],
(C.[Positions_without_ready_back-up]/A.[Leader_Positions])*100 AS
[Percent_without_ready_back-up]
FROM
(SELECT Count([t_Eng Leadership Master].[Leader Name]) AS
[Leader_Positions]
FROM (SELECT DISTINCT [Leader Name] FROM [t_Eng Leadership Master])
AS [Leadership Position Data]) AS A,
(SELECT Count([t_Eng Leadership Master].[Leader Name]) AS
[Positions_without_back-up]
FROM (SELECT DISTINCT [Leader Name] FROM [t_Eng Leadership Master]
WHERE [Candidate Name] IS NULL) AS [Leadership Position Data]) AS B,
(SELECT Count([t_Eng Leadership Master].[Leader Name]) AS
[Positions_without_ready_back-up]
FROM (SELECT DISTINCT [Leader Name] FROM [t_Eng Leadership Master]
WHERE [Readiness] <> "Now") AS [Leadership Position Data]) AS C;

 

[Scott]

Excellent! It's just the help I needed...I really appreciate it.

Scott