Running query for multiple ID's

  • Thread starter Thread starter brandon
  • Start date Start date
B

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
 
SELECT TOP 20 Table3.PhoneExtensionID, Count(Table3.PhoneExtensionID) AS
CountOfPhoneExtensionID
FROM Table3
GROUP BY Table3.PhoneExtensionID
ORDER BY Count(Table3.PhoneExtensionID) DESC;
 
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]
 
Back
Top