Sub Query Help!

[jeancake]

Hello Message Board.

I would like some help on an analysis that I'm doing. I have an Access table of accounts. I created an SQL statement that gives me the the top 5 Sales Reps based on the number of accounts opened in the last 12 months.

What I would like to do is use the results that query to provide a cross tab query that would give me a monthly trend of account openings for those five Reps.

Any help would be greatly appreciated.

 

[xenou]

Hi, in general, you want to use the results of the first query in your where clause in a new query. I assume the reps have an ID or Rep Number to identify them - we assume here that this is a single identifying field.


SELECT Count(NewAccountStuff), OtherField1, OtherField2 FROM MyTable
WHERE RepID IN (
SELECT DISTINCT RepID From MyTop5Query
)

Of course when this result is good you cross tab it (which I usually do in the query by design grid rather than writing by hand).

 

[jeancake]

Got it!! Thank you very much!

 

[Fazza]

FWIW, how I'd do it. Which is always in Excel, btw, but should be the same. I understand this may be unneeded, but it is welcome SQL practice for me.

the data for the top 5 would be

SELECT A.RepID, A.AccountOpenDate
FROM table A, (
SELECT TOP 5 COUNT(*), B.RepID
FROM table B
WHERE Date - B.AccountOpenDate < 366
GROUP BY B.RepID
ORDER BY 1 DESC) C
WHERE A.RepID = C.RepID

You could have an INNER JOIN instead of the style I've used. You could summarise the counts by month in this query, but I've done that in the cross-tab.

Cross-tab,

TRANSFORM COUNT(*)
SELECT RepID
FROM (all of text from first query)
GROUP BY RepID
PIVOT Year(AccountOpenDate) + MONTH(AccountOpenDate)/100

Or to spell it all out,

TRANSFORM COUNT(*)
SELECT RepID
FROM (SELECT A.RepID, A.AccountOpenDate
FROM table A, (
SELECT TOP 5 COUNT(*), B.RepID
FROM table B
WHERE Date - B.AccountOpenDate < 366
GROUP BY B.RepID
ORDER BY 1 DESC) C
WHERE A.RepID = C.RepID)
GROUP BY RepID
PIVOT Year(AccountOpenDate) + MONTH(AccountOpenDate)/100