Find top 3 variables

[NISMOJim]

I am making some improvements to the very first database I created a few years back, when I knew slightly less about programming than I do now, trying to automate more tasks. The code in my module contains several variables calculated from the table (total amount of fuel used during a specified month by each user, etc.). What I would like to do is find the top 3 users, for instance, I know from earlier code in this module that...

Team1 = 3000 (gallons)


Team 2 = 1500
Team 3 = 800
Team 4 = 2000
...Team 30 = 3500

How can I find the top 3 variables, so that what gets sent out in an e-mail (later in the module) will say...

"The top 3 users were Team 30, Team 1 and Team 4."

[orange]

see http://support.microsoft.com/kb/153747

[NISMOJim]

This kinda makes sense to me if the values were already set in the table/query. The code I'm working on calculates the total of all fuel transactions for the month by each group already, now I just need to figure out the top three. The code I used to get the totals is...

'Calculate total amount of fuel used by Test Groups last month
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'**NVH Group**
strSQLTotalNVH = "Select Sum(Quantity) AS SumOfQuantity FROM Transactions WHERE Date between #" & LastDateStart & "# And #" & LastDateEnd & "# And [Account Number] = 50005010;"
Set r = CurrentDb.OpenRecordset(strSQLTotalNVH)
'check for records
If Not (r.BOF And r.EOF) Then
TotalNVH = Nz(r("SumOfQuantity"))
Else
TotalNVH = 0
End If
r.Close
Set r = Nothing

Now I have a value for the variable TotalNVH. This code is run for each group, (each with it's own account number) so in the end I have several variables with a value assigned in the code. As an extra wrench in the system, not all accounts in the table are included in the top 3 results, regardless of their total (that's why I broke them out separately, just the ones I need to include).
So now that I have all of these variables running around in my code with each group's total usage, is there another way to figure the top 3 out of them, or is there a better way to write the SQL statement that would only pull the top 3 accounts that should be included?

[NISMOJim]

One other thing with this code...In the e-mail body I need to force a new line. I've tried vbCrLf, Chr(13) & vbNewLine, but nothing is working. Is there something in the reference library that needs to be checked? I thought if that was the case, I would get an error message saying the reference library is missing or something like that.

[NISMOJim]

Here is the e-mail part of the same code...

MsgBody = MsgBody & " Main users of fuel were " & TotalDUR & " (" & PercentDUR & "%), " & TotalCHA & " (" & PercentCHA & "%), and " & TotalPWR & " (" & PercentPWR & "%)." & vbCrLf & Chr(13)
MsgBody = MsgBody & "Fuel Usage by Department - Top 3 users of fuel were " & TotalDUR & " (" & VETPercentDUR & "%), " & TotalCHA & " (" & VETPercentCHA & "%) and " & TotalOPS & " (" & VETPercentOPS & "%), accounting for " & vbCrLf & "% of the total VE/T usage."


Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.BodyFormat = olFormatRichText
.To = "LD@nis.com"
.Subject = "Monthly Tracking - Fuel"
.HTMLBody = MsgBody
'.DeleteAfterSubmit = True 'This would let Outlook send the note without storing it in your sent bin
.Display
End With

Since we're dealing with mysteries, how come I can't force a new line with vbCrLf, Chr(13) or even vbNewLine?

[NISMOJim]

Disregard the last 2 posts, I found the answer about forcing a new line, "<br>" did the trick. I also discovered that you can make some of the text bold, which I had no idea you could do. Now, back to my original problem in simplified form...

MyVariableA = SQL the sum of a bunch of transactions from a table that meet a criteria.
MyVariableD = SQL the sum of a bunch of transactions from the same table that meet a different criteria.
...
...
MyVariableW = SQL the sum of a bunch of transactions from the same table that meet the last criteria.

Of all my variables (about 20), I need to find out which three have the highest numbers. How would I write code to find the answer?

Thanks again!