Pulling and Sorting Records on a report.

[farnorth]

I am working on a DB that sorts data based on certain criteria. It is for a car race track. Here is an example.

Records...

Name Car Class Position Time Date
D. Smith 33K Open 1 10:230 12/30/02
J. Smith 55X Open 2 10:235 12/30/02
L. Jones 22Z Open 3 10:239 12/30/02

What I need is to be able to either Query or Report pulling positions into different categories. For example.

Heat Race 1
Name Car Class Position Time Date
D. Smith 33K Open 1 10:235 12/30/02
L. Jones 22Z Open 3 10:239 12/30/02

Heat Race 2
Name Car Class Position Time Date
J. Smith 55X Open 2 10:230 12/30/02

How it works is position 1 goes to Heat One Position 2 Goes to Heat 2. Position 3 Goes to Heat 1 etc...
It swops positions amoung 1 thru 5 Heat races... Confused yet? It is like handing out playing cards to up to 5 people, you start with the first person and give a card to each. When you reach the last person you start over with the first till all your cards are handed out.

Thanks in advance for the help.
Bob

 

[SgtJarrow]

Put the following code into a new module. Save it as whatever you want. name really doesn't matter as long as you don't name it SelectHeat. Then, build you query with the data from your field. Insert a blank field at the beginning of the query and put:

Heat: SelectHeat([Poisition])

in the field line for this blank field. I also sorted this field and the position field ascending in the query and produced results exactly as you requested. Let me know if you have any problems.

' **************** Start Code *****************
Public Function SelectHeat(intPosition As Integer) As Integer

Select Case intPosition
Case 1, 6: SelectHeat = 1
Case 2, 7: SelectHeat = 2
Case 3, 8: SelectHeat = 3
Case 4, 9: SelectHeat = 4
Case 5, 10: SelectHeat = 5
End Select

End Function
' ************** End Code ****************** Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCP, Network+, A+
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com