Ranking records on a report 1

[bernie10]

Hello,

I have a query that selects the three cheapest products from a table. I would like to generate the report which ranks the three products. I know how to create the report off the query but I don't know how to modify the report to explicitly specify the cheapest product as the winner. So basically I am asking how to add the word "winner" for the cheapest record, "second place" for the second record, and "third place" for the third record. I would also like to display the winning record in red if possible.

The report could be as simple as:

Product Price
Winner: B 25.52
Second Place: A 26.24
Third Place: C 29.75

Any suggestions?

Thanks.

 

[dhookom]

Add a text box to the detail section:
Name: txtOrder
Control Source: =1
Running Sum: Over All

Add another text box:
Name: txtPlace
Control Source: =Choose([txtOrder],"Winner","Second Place","Third Place")

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Me.txtOrder = 1 Then
        Me.txtPlace.ForeColor = vbRed
        Me.txtPrice.ForeColor = vbRed
        Me.txtProduct.ForeColor = vbRed
     Else
        Me.txtPlace.ForeColor = vbBlack
        Me.txtPrice.ForeColor = vbBlack
        Me.txtProduct.ForeColor = vbBlack
    End If
End Sub

Duane
MS Access MVP
Find out how to get great answers faq219-2884.