Add auto-number to query results

[bkf7911]

I'm working on Top N queries, and need to have a number field so that I can limit the results to 5. I know i can change the properties of the query to be a Top Values, but in the event of a tie, the results include the tied values, making it more than 5.

Is there a formula I can add as a field that will tell me the row number of the results? It won't trouble me if its generated every time, because thats what i need.

 

[lespaul]

you mean a ranking query?

1 other fields
2 other fields

this post thread701-1368370 has a suggestion, you can also search the forum/FAQ area for rank or ranking query and find some other examples.

HTH



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[PHV]

You should have a combination of fields unique for each row.
What is your actual table(s) schema and the SQL code of the query you want to rank ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bkf7911]

i just want to number each line in the results of the query and restrict output to where that number is less than 5. why's it gotta be so round-about to arrive at a top N query when you have more than one Grouping?

INSERT INTO Results ( State, MARKET, FCID_MARKET, Violation, [Loss Type], SumOfAmount, [Policy Type], [NPV Viol Sum], [PV Viol Sum], [PV % Total], [NPV % Total], [Count] )
SELECT TOP 5 [Base Info].State, [Base Info].MARKET, [Base Info].[FCID Market], IIf(Forms!Menu.Combo0 Is Null,Null,(IIf(Forms!Menu.Combo3 Like "Yes",Null,[Violation Type]))) AS Violation, IIf(Forms!Menu.Combo3 Like "Yes",[LOSS_TYPE_DESC],(IIf(Forms!Menu.Combo3=No,""))) AS [Loss Type], Sum([Base Info].Amount) AS SumOfAmount, IIf(Forms!Menu.Combo0 Is Null,Null,[Policy]) AS [Policy Type], [Base Info].[NPV Viol Sum], [Base Info].[PV Viol Sum], [Amount]/[PV Viol Sum] AS Expr1, [Amount]/[NPV Viol Sum] AS Expr2, Count(*) AS Expr3
FROM [Base Info]
GROUP BY [Base Info].State, [Base Info].MARKET, [Base Info].[FCID Market], IIf(Forms!Menu.Combo0 Is Null,Null,(IIf(Forms!Menu.Combo3 Like "Yes",Null,[Violation Type]))), IIf(Forms!Menu.Combo3 Like "Yes",[LOSS_TYPE_DESC],(IIf(Forms!Menu.Combo3=No,""))), IIf(Forms!Menu.Combo0 Is Null,Null,[Policy]), [Base Info].[NPV Viol Sum], [Base Info].[PV Viol Sum], [Amount]/[PV Viol Sum], [Amount]/[NPV Viol Sum]
HAVING ((([Base Info].State)="GA") AND (([Base Info].MARKET)="GA BUS") AND (([Base Info].[FCID Market])="GA CTR") AND ((IIf([Forms]![Menu].[Combo0] Is Null,Null,[Policy])) Like [Forms]![Menu].[Combo0])) OR ((([Base Info].State)="GA") AND (([Base Info].MARKET)="GA BUS") AND (([Base Info].[FCID Market])="GA CTR") AND (([Forms]![Menu].[Combo0]) Is Null))
ORDER BY Sum([Base Info].Amount) DESC;

 

[lespaul]

why's it gotta be so round-about to arrive at a top N query when you have more than one Grouping?

How is the database engine suppose to decide which one you want when there's a tie?

First suggestion, Access uses the HAVING clause incorrectly when you use an aggregate function in the SELECT clause, you can move all those criteria to the WHERE clause.

Secondly, did you look at the thread I posted? You'll notice that there's a section of the query posted that has this:
[tt](Select Count (*) FROM [CCL_EST_NM] as Temp
WHERE [Temp].[est_seq] < [CCL_EST_NM].[est_seq])[/tt]

and if you look at the table sample there's a field est_seq that has the values:
[tt]
10
11
12
13[/tt]
The WHERE clause in the INNER QUERY above says count the number of rows where the est_seq id is less than the est_seq field in the OUTER query (this is referred to as a Correlated Sub Query). So if the outer query has an est_seq of 10, the INNER query counts the number of records that have an est_seq less than 10, in this sample that count would be 0. Then if the OUTER query has an est_seq of 11, then the INNER query returns a count of 1 record that has an est_seq LESS THAN 11 (the record that has an est_seq of 10).

So, what identifier do you have that can be used to "rank" the query results?


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[bkf7911]

To the first point, couldn't move the "Having" section to be "where". Gave an error. I'm ok with the design as is, even if i don't get my auto-number.

To the second point, yes, i did look at your link and the preceding instructional. Looking at it, and understanding it, and working it into my query are 3 very different things for me.

In trying to work your logic into this query, i added an auto-number field to hopefully use as the "est_seq" piece. I'll be re-creating the base info table on a regular basis, and auto-number seemed as if it would save time in the future.

Anyway. I really appreciate the help, but am moving on to other fish. Thank again.