Running query for multiple ID's

[brandon]

Hello all:

I am having troubles figuring out how to format a query.
Here is what I'm trying to do:

I have a table with Phone Extension ID's. I want a query
to give me the top 20 most frequently called numbers for
each Phone Extension ID. How do I go about running the
query for each ID without making a new query for each
Extension ID? I want it all to come out into one report
if possible, with each extension having it's own page in
the report.

I have figured out all of the counting for finding the
most frequent calls, and average duration time, but I
can't get it to run for each ID.

Any help or point in the right direction would be greatly
appreciated.

Thanks in advance.

Brandon

 

[JohnFol]

SELECT TOP 20 Table3.PhoneExtensionID, Count(Table3.PhoneExtensionID) AS
CountOfPhoneExtensionID
FROM Table3
GROUP BY Table3.PhoneExtensionID
ORDER BY Count(Table3.PhoneExtensionID) DESC;

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
[Extensions].[Extension ID],
[Extensions].[Extension Name],
[Log].[Phone Number],
AVG([Log].[Call Length]) AS [Average Call Length]
FROM
[Extensions]
INNER JOIN
[Log]
ON
[Extensions].[Extension ID] = [Log].[Extension ID]
WHERE
[Log].[Phone Number] IN
(SELECT TOP 20
[Self].[Phone Number]
FROM
[Log] AS [Self]
WHERE
[Self].[Extension ID] = [Extensions].[Extension ID]
GROUP BY
[Self].[Phone Number]
ORDER BY
COUNT(*) DESC)
GROUP BY
[Extensions].[Extension ID],
[Extensions].[Extension Name],
[Log].[Phone Number]