SQL - creating a rank

[diagostj]

I'm working with a voter history file, and I have a column that contains the number of times each person voted in the last five elections. I'd like to create a second column from that one, giving them rank. (ie - if they voted in 5, they get a "1" if they voted in 4, they get a "2" etc)

Usually I only do this for three elections, and the guy who used to volunteer here had already come up with a code for the query to make it give rank. I've just been using that code, copying and pasting it into my new queries. I don't know SQL offhand, though, at least not well enough to figure out the logic behind the code he's written. It works just fine for three, but I really don't know what it is I'm doing.

This is the code that I have now.

Prime: IIf([PrimeTot]=0,"- ",IIf([PrimeTot]<>2,IIf([PrimeTot]=3,1,3),2))

Here's the thing... I would like someone to help me modify that (or give me something better?) but more importantly, explain what it is you're doing so that I can actually learn it, rather than just mimicking it. I would really appreciate any suggestions.

Thanks so much!!

 

[mdemarte]

Maybe I am making this too simple, but isn't
rank = 6 - [Primetot]?
5 to 1
4 to 2
3 to 3
etc.

At least, that is what it looks like from the data you listed.

However, I think all you need to do is sort by Primetot in Descending order. Then, all of your 5's are listed first, your 4's are next, etc.

 

[raskew]

The Choose() function [look it up in the help file] is particularly applicable when there is 1,2,3,4,5 sort of response. Try experimenting with this in the debug window:

PrimeTot = 5
Prime = Choose(PrimeTot + 1, "-", "5", "4", "3", "2", "1" )
? prime
1

You can manually test it in the debug window by incrementally substituting the value of PrimeTot from 5 to 0, then stepping thru the next two steps.

 

[diagostj]

However, I think all you need to do is sort by Primetot in Descending order. Then, all of your 5's are listed first, your 4's are next, etc.

I don't want to sort it by prime total, I want to sort it by town and district, but I want the people who voted most frequently to have the lowest numbers.

I should explain... We'll print out a page with these names and phone numbers, and we'll call the people who voted most FIRST. Hence, I want the 5s to be 1s and the 4s to be 2s... The order in which we will call them.

 

[fuzzygeek]

Use a function in the query

In the query, use a function that sends the number of times the voter voted. The function returns a number reversed 1 to 6, 6 being no vote, 1 means voted every time. You can adjust this to fit your particular circumstances.

In the query do something like this
TheRank: fncRankOrder([Voted])
where [Voted] is the field in your case that tells how many times they voted.

The function:

Function fncRankOrder(intRankNumb As Integer) As Integer
On Error GoTo Error_Process
'Declaration
Dim intVoteNumb As Integer
'Initialize
intVoteNumb = 6

'Using a select case, the lo shall be hi and
'the hi and the hi shall be lo
Select Case intRankNumb
Case 1
intVoteNumb = 5
Case 2
intVoteNumb = 4
Case 3
intVoteNumb = 3
Case 4
intVoteNumb = 2
Case 5
intVoteNumb = 1
Case Else
intRankNumb = 6

End Select

'Return the new ranking
fncRankOrder = intVoteNumb

Exit_Process:
Exit Function
Error_Process:
MsgBox "The error is " & Err & " - " & Err
Resume Exit_Process
End Function

 

[Tiro Cupidus]

Prime: IIf([PrimeTot]=0,"- ",IIf([PrimeTot]<>2,IIf([PrimeTot]=3,1,3),2))

This is using the IIf() function (obviously), which works like this: IIf(condition, do this if condition is true, do this if condition is false). The Prime before the colon is simply naming the query field.

So basically it looks like this:

If PrimeTot=0 Then
  Display this: "- "
Else
  If PrimeTot <> 2 Then '(<> means not equal to)
    If PrimeTot=3 Then
      Display this: "1"
    Else
      Display this: "3"
    End If
  Else
    Display this: "2"
  End If
End If

Hope that helps you understand it a little better.

 

[diagostj]

These are all helpful in understanding what it's doing and what's going on, but how would I apply it?

For example, I've never done a function in Access before. How would one go about inputting that in the "field" row?

 

[Tiro Cupidus]

Usually you have just a field name in there, which also appears as the name of the column when the query is in datasheet view. If you paste

Prime: IIf([PrimeTot]=0,"- ",IIf([PrimeTot]<>2,IIf([PrimeTot]=3,1,3),2))

into the field of a new column in design view, then in datasheet view you will have a new column, 'Prime', which displays the results of the function(s).

 

[fuzzygeek]

In your terms

"In the query do something like this
TheRank: fncRankOrder([Voted])
where [Voted] is the field in your case that tells how many times they voted. " ...What this means is in the field row type is:


Prime: fncRankOrder([PrimeTot])


.......
PrimeTot is sent to the function as intRankNumb, shown in the function. Then it is ran through the function. If PrimeTot is 5, then it goes to Case 5 where the variable, intVoteNumb, is set to 1. Near the end of the function, the variable, intVoteNumb, assigns the value 1 to the function name, which is defined as an integer. This returns the value 1 to the row variable Prime. You are sending a variable (PrimeTot) just like you did in the IIf statement and getting a resultant (Prime) just like the IIf statement.

The function is placed in a module in the module section. The function is fncRankOrder as shown in my previous reply.

 

[fuzzygeek]

Perhaps a better illustration

Let me explain by this example. 1 and 2 produce the same results in the query


(1) TheTruth: IIF([aTruth] = True, False, True)

(2) TheTruth: fncReverseTruth([aTruth])



Function fncReverseTruth(Truth As Boolean) As Boolean
On Error GoTo Error_Process

'Declaration
Dim MyTruth As Boolean

'A lie shall be the truth and the truth a lie
If Truth = True Then
MyTruth = False
Else
MyTruth = True
End If

'Send the results back to the calling query element
fncReverseTruth = MyTruth

Exit_Process:
Exit Function
Error_Process:
MsgBox "The error is " & Err & " - " & Error
Resume Exit_Process
End Function

Study both of these and you'll see that they are doing the same thing. Unfortunately, the more embedded the IIf, the more complicated it is to follow. That is why I used a Case statement in the other function. You would have a five deep IIf statement otherwise.

 

[Tiro Cupidus]

Yeah, for this it is much less complicated to use Select Case. As long as you understand how to refer to that function from within the query, which is what I think you were asking about.