Lookup Query

[MTBChik]

I am creating a report that will list the participants in a race.

Ex.
John Smith, M, 20-29, 1:03:41
Chuck Jones, M, 20-29, 1:04:00
Bill Watson, M, 30-39, 1:03:15
Jill Johnson, F, under 40, 1:20:02

I've built queries to sort by age class and ascending time, but I need to calculate their place within class as well as time back. Any suggestions on how to do this within a query or the report itself?

Thanks!

 

[jsolutions]

If I understand correctly, your classifications are age groups (or age and gender) and you want to find who came in first in the 20-29 category, who came in second, etc.

If this is correct, then in your report, you need to group by Classification and specify the sort order on the time.

The report wizard will let you do this fairly easily.

If I misunderstood what you are looking for or if you have any questions, let me know.

jay

 

[MTBChik]

Basically, I need places 1-x to print out next to the person's name, with the time back next to their time

Final Data Needed:
Place,Name,Gender,Age,Time,Time Back
1. John Smith, M, 20-29, 1:03:41, --
2. Chuck Jones, M, 20-29, 1:04:00, :19
1. Bill Watson, M, 30-39, 1:03:15, --
1. Jill Johnson, F, under 40, 1:20:02,--
2. Sarah Adams, F, under 40, 1:22:37,2:35

Original Data From tblRaceDB
John Smith, M, 20-29, 1:03:41
Chuck Jones, M, 20-29, 1:04:00
Bill Watson, M, 30-39, 1:03:15
Jill Johnson, F, under 40, 1:20:02

Comprende?

 

[jsolutions]

Got it. You just need to create an integer variable. In the On Print Event of your grouping, set the variable to zero. In the On Print Event of the detail section, add one to the variable. Then in the detail section of your report, use a function to get the value of the integer value(see code below).

DIM i as INTEGER
[i]Private Sub GroupHeader2_Print(Cancel As Integer, PrintCount As Integer)
    i=0
End Sub[/i]
 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    i = i + 1
End Sub 

Public Function GetPlace()
    GetPlace = i
End Function

So, in the detail section, you would create a textbox whose control source is GetPlace. Then you would add in the textboxes for Name, time, age group, etc.

The code above will reset the "counter" each time a new group is entered and should give you the results you are looking for.

Let me know if you have any questions.

Jay