Multi possibilities for sort order 4

[bustersports]

I have written several easy reports that indicate results of surveys. There are only 4 fields of results. The requestor would like to indicate on each which was the highest score, but always have the answer showing in the proper field (see below for example). Does anyone have any idea on how to make this work without going thru a lot of IIF statements? Thanks in advance for your help.

David

Current report

Period Type A Type B Type C Type D
Q1 2005 3.1 2.8 2.9 3.3
Q2 2005 2.9 3.2 3.2 3.4

Desired report
Period Type A Type B Type C Type D
Q1 2005 (2) 3.1 (4) 2.8 (3) 2.9 (1) 3.3
Q2 2005 (4) 2.9 (2) 3.2 (2) 3.2 (1) 3.4

The () indicates the rank among the score for the period, so in Q1, Type D was the highest score with Type B being the lowest. Any help is greatly appreciated.

 

[PHV]

In a standard code module you may create your own function:

'A generic function to get the rank of an element in an arbirtrary numbers of same type values:
Public Function myRank(Elem, ParamArray Args())
If IsNull(Elem) Then Exit Function
Dim i As Long, j As Long, tmp
For i = 0 To UBound(Args) - 1
  For j = i + 1 To UBound(Args)
    If Args(i) < Args(j) Then
      tmp = Args(j): Args(j) = Args(i): Args(i) = tmp
    End If
  Next
Next
For i = 0 To UBound(Args)
  If Elem = Args(i) Then myRank = i + 1: Exit Function
Next
End Function

And now your query:
SELECT Period, myRank([Type A], [Type A],[Type B],[Type C],[Type D]) AS RankA, [Type A], ..., myRank([Type D], [Type A],[Type B],[Type C],[Type D]) AS RankD, [Type D]
FROM ...

Or you may call this function in the Format event procedure of the relevant section.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tranman]

David,
Looks like fun.

I think what I'd do is, in the Format event of the Detail section, create a stand-alone recordset with three columns (name of the control where the final answer goes, value and rank). Then I'd load the recordset with the control names and values and sort it on the values. Then you could store off the value and rank(1) of the first entry. Then pass through the recordset from top to bottom. If the value was the same as the stored value, you would assign the same rank and movenext. If the value is larger, assign the next larger rank (stored rank + 1) and store off the value and rank and movenext. (This would take care of value dupes).

When you get to the end of the recordset, you have each control name associated with a value and a rank. At that point, you can just plug the values and ranks back into the controls.

In reading back over this, it sounds a lot more complicated than it is. I'm thinking not more than 30 or 40 lines of code and the overhead would be miniscule. If you don't get a better suggestion (I'm sure there are other, better ways), write back and I'll be glad to bang out some code to do what you're wanting.

Tranman

 

[bustersports]

Tranman

Any additional help you can give would be greatly appreciated. I am not quite sure how to go about doing what you are suggesting.

Thanks again.
David

 

[Tranman]

David,
Here you go. Busy today, so had to wait for afternoon break.

1) The linked fields on the report are named TYPE_A, TYPE_B, TYPE_C, TYPE_D. They are pushed off to the side somewhere, and are marked "not visible".

2) There are 4 other fields on the report, named TYPE_Atxt, TYPE_Btxt, TYPE_Ctxt, TYPE_Dtxt. These unbound fields are the ones that will be populated with the rank and values, and will be visible on the report.

You could make the unbound fields just contain the rank, and put them beside the linked fields and make the linked fields visible. I just didn't choose to do it that way.

It basically works like I said.
Stores the values in the recordset and then sorts the rs rows descending.

Assigns ranking to each row, allowing for duplicated values.

Passes back through the recordset assigning values to the different unbound controls, based upon their names, and the names stored in the recordset.

I have tested it with several different value sets and it seems to work in all of them.

It did turn into more than "30 or so" lines of code, but they always do, don't they?

Good luck with your project.

Let me know if something doesn't work right.

Oh yeah, I put it in the print event instead of the format.

Tranman

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim ctrl As Control
Dim intInc As Integer
Dim intRank As Integer
Dim sglValue As Single
Dim rs As New ADODB.Recordset
'Add new recordset fields
rs.Fields.Append "NAME", adChar, 6
rs.Fields.Append "VALUE", adSingle
rs.Fields.Append "RANK", adSingle
rs.Open

'Add the new rows to the recordset
rs.AddNew
rs.Fields("NAME") = "TYPE_A"
rs.Fields("VALUE") = Me.TYPE_A
rs.AddNew
rs.Fields("NAME") = "TYPE_B"
rs.Fields("VALUE") = Me.TYPE_B
rs.AddNew
rs.Fields("NAME") = "TYPE_C"
rs.Fields("VALUE") = Me.TYPE_C
rs.AddNew
rs.Fields("NAME") = "TYPE_D"
rs.Fields("VALUE") = Me.TYPE_D

'Sort the recordset descending
rs.Sort = "VALUE desc"

'Assign the first rank and move to the next row
rs.MoveFirst
intRank = 1
sglValue = rs.Fields("VALUE")
rs.Fields("RANK") = 1
rs.Update
rs.MoveNext

'Assign the subsequent ranks
Do While Not rs.EOF
  Select Case rs.Fields("VALUE")
    Case Is = sglValue
      rs.Fields("RANK") = intRank
      intInc = intInc + 1
      sglValue = rs.Fields("VALUE")
    Case Else
      intRank = intRank + intInc + 1
      rs.Fields("RANK") = intRank
      sglValue = rs.Fields("VALUE")
      intInc = 0
  End Select
  rs.Update
  rs.MoveNext
Loop

'Plug the values into the unbound controls
rs.MoveFirst
Do While Not rs.EOF
  For Each ctrl In Me.Controls
    If Left(ctrl.Name, 6) = rs.Fields("NAME") And Right(ctrl.Name, 3) = "txt" Then
      ctrl = "(" & rs.Fields("RANK") & ") " & rs.Fields("VALUE")
    End If
  Next
  rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Sub

 

[bustersports]

Tranman

Thanks for all your help. Works great.

 

[PHV]

bustersports, just to know, have you tried my suggestion ?

 

[bustersports]

PHV

Yes, thanks very much. Saw yours and Tranman at the same time, both worked.

 

[Tranman]

PHV,
Nice piece of work. I have been using bubble sorts for about a zillion years (it seems), but had never put one together with a variable-length ParamArray like you did.

I liked your code so much I added it to my "Tips and Tricks" file.

Have a star!

Tranman