Top 10 of each

[MNY Intern]

Right now I have probably over 20 queries to get what I'm looking for, and
I'm convinced theres an easier way of doing it...

I have a table with all the fines all the cars in our company incurs. I want
to use 1 query to get the top ten fine incurring cars between date 1 and date
2 from each center, there are 18 centers.

For example:

If I wanted to do top 2--
Center 1 has cars 1-5 each incurring fines daily.
Center 2 has cars 6-10, etc as shown in my table

I specify a range of dates, get the 2 cars from each center that have the
greatest total $amount in fines for that period of time.

Right now my SQL for 1 query that does top 10 for 1 center looks like this:

SELECT TOP 10 [NEW TICKETS].[CTR NAME], [NEW TICKETS].CENTER, [NEW
TICKETS].CARNO, Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
FROM ([NEW TICKETS] INNER JOIN [Base Period Rundates] ON [NEW
TICKETS].RUNDATE = [Base Period Rundates].RUNDATE) INNER JOIN VEHICLE ON [NEW
TICKETS].CARNO = VEHICLE.CARNO
GROUP BY [NEW TICKETS].[CTR NAME], [NEW TICKETS].CENTER, [NEW TICKETS].CARNO
HAVING ((([NEW TICKETS].CENTER)="1010"))
ORDER BY Sum([NEW TICKETS].[new fine]) DESC;

 

[KARL DEWEY]

Try summing, sorting descending, ranking, and set criteria on ranking to get
top.

 

[MNY Intern]

Can you be a little more specific on how to accomplish that?

Try summing, sorting descending, ranking, and set criteria on ranking to get
top.

Right now I have probably over 20 queries to get what I'm looking for, and
I'm convinced theres an easier way of doing it...

I have a table with all the fines all the cars in our company incurs. I want
to use 1 query to get the top ten fine incurring cars between date 1 and date
2 from each center, there are 18 centers.

For example:

If I wanted to do top 2--
Center 1 has cars 1-5 each incurring fines daily.
Center 2 has cars 6-10, etc as shown in my table

I specify a range of dates, get the 2 cars from each center that have the
greatest total $amount in fines for that period of time.

Right now my SQL for 1 query that does top 10 for 1 center looks like this:

SELECT TOP 10 [NEW TICKETS].[CTR NAME], [NEW TICKETS].CENTER, [NEW
TICKETS].CARNO, Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
FROM ([NEW TICKETS] INNER JOIN [Base Period Rundates] ON [NEW
TICKETS].RUNDATE = [Base Period Rundates].RUNDATE) INNER JOIN VEHICLE ON [NEW
TICKETS].CARNO = VEHICLE.CARNO
GROUP BY [NEW TICKETS].[CTR NAME], [NEW TICKETS].CENTER, [NEW TICKETS].CARNO
HAVING ((([NEW TICKETS].CENTER)="1010"))
ORDER BY Sum([NEW TICKETS].[new fine]) DESC;

 

[John Spencer]

Since you have spaces in your field names and table names, I think you will
have to use two queries. You can try this.

SELECT NT.CARNO as VehNo, NT.CENTER, Sum([NT].[new fine]) as
TotalFines
FROM ([NEW TICKETS] As NT INNER JOIN [Base Period Rundates]
ON [NT].RUNDATE = [Base Period Rundates].RUNDATE)
INNER JOIN VEHICLE ON [NT].CARNO = VEHICLE.CARNO
WHERE NT.CENTER = [NEW TICKETS].CENTER
GROUP BY NT.Center, [NT].CARNO
Save that as qFines

SELECT [NT].[CTR NAME]
, [NT].CENTER
, [NT].CARNO
, Sum([NT].[new fine]) AS [SumOfnew fine]
FROM ([NEW TICKETS] as NT INNER JOIN [Base Period Rundates]
ON [NT].RUNDATE = [Base Period Rundates].RUNDATE)
INNER JOIN VEHICLE ON [NT].CARNO = VEHICLE.CARNO
WHERE NT.CarNo In (
SELECT TOP 10 qFines.VehNo
FROM QFines
WHERE QFines.Center = NT.Center
ORDER BY qFines.TotalFines Desc )

GROUP BY [NT].[CTR NAME]
, [NT].CENTER
, [NT].CARNO

 

[KARL DEWEY]

I named your query [Top Tickets] and then ran this one.

SELECT Q.[CTR NAME], Q.CENTER, Q.CARNO, Q.[SumOfnew fine], (SELECT COUNT(*)
FROM [Top Tickets] Q1
WHERE Q1.

= Q.

AND Q1.[SumOfnew fine] > Q.[SumOfnew fine])+1 AS Rank
FROM [Top Tickets] AS Q
WHERE ((((SELECT COUNT(*) FROM [Top Tickets] Q1
WHERE Q1.

= Q.

AND Q1.[SumOfnew fine] > Q.[SumOfnew fine])+1)<3))
ORDER BY Q.[SumOfnew fine] DESC , Q.[CTR NAME], Q.CENTER, Q.CARNO;

Can you be a little more specific on how to accomplish that?

Try summing, sorting descending, ranking, and set criteria on ranking to get
top.

Right now I have probably over 20 queries to get what I'm looking for, and
I'm convinced theres an easier way of doing it...

I have a table with all the fines all the cars in our company incurs. I want
to use 1 query to get the top ten fine incurring cars between date 1 and date
2 from each center, there are 18 centers.

For example:

If I wanted to do top 2--
Center 1 has cars 1-5 each incurring fines daily.
Center 2 has cars 6-10, etc as shown in my table

I specify a range of dates, get the 2 cars from each center that have the
greatest total $amount in fines for that period of time.

Right now my SQL for 1 query that does top 10 for 1 center looks like this:

SELECT TOP 10 [NEW TICKETS].[CTR NAME], [NEW TICKETS].CENTER, [NEW
TICKETS].CARNO, Sum([NEW TICKETS].[new fine]) AS [SumOfnew fine]
FROM ([NEW TICKETS] INNER JOIN [Base Period Rundates] ON [NEW
TICKETS].RUNDATE = [Base Period Rundates].RUNDATE) INNER JOIN VEHICLE ON [NEW
TICKETS].CARNO = VEHICLE.CARNO
GROUP BY [NEW TICKETS].[CTR NAME], [NEW TICKETS].CENTER, [NEW TICKETS].CARNO
HAVING ((([NEW TICKETS].CENTER)="1010"))
ORDER BY Sum([NEW TICKETS].[new fine]) DESC;