Subqueries pt. 2

[Scott]

Thanks to (e-mail address removed) for the response on the subqueries. I
now need to take this a step further and add a couple of calculated
fields into the mix. I've tried a variety of versions this morning, but
I'm getting nowhere. Can someone help?

Here's where I stand:

SELECT
A.[# of Positions],
B.[# of Leader Positions with no planned back-up],
C.[percent of Leader Positions with no planned back-up],
D.[# of Leader Positions with no ready back-up],
C.[percent of Leader Positions with no ready 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,

CALCULATION FOR C WOULD GO HERE... WOULD BE THE COLUMN B RESULT /
COLUMN A RESULT.

(SELECT Count([t_Eng Leadership Master].[Leader Name]) AS [# of Leader
Positions with no ready back-up]
FROM (SELECT DISTINCT [Leader Name] FROM [t_Eng Leadership Master]
WHERE [Readiness] <> "Now") AS [Leadership Position Data]) AS D;

CALCULATION FOR E WOULD GO HERE... IT WOULD BE THE COLUMN D RESULT /
COLUMN A RESULT.

Can someone make a suggestion on how to handle the calcuation
subqueries?

Thanks!

Scott

 

[Tom Ellison]

Dear Scott:

Several recommendations for you:

1. Use subqueries in the SELECT clause, too

2. Show some sample data and expected results in your post

3. Make some explanation of what the query is to do

4. Use indentation to make queries more readable

5. Use more reasonably short table and column names, with no spaces or #'s
in them

6. Check to see parens are balanced

How about this:

SELECT TOP 1
(SELECT COUNT(*)
FROM (SELECT DISTINCT [Leader Pernr]
FROM [t_Eng Leadership Master])) AS NumberOfPositions
FROM [t_Eng Leadership Master]

This is intended to return only the first column you wanted. Does it work?
Can you extend it to return other columns?

Tom Ellison