Need to count Selections in a query

[Diggsy]

I have a database that allows People to select 4 Golfers. I have a Table
Called Pick That has a AutoNumber (Primary) field named PickNo and Name and I
have a Table called Choice that has a AutoNumber (Primary) ChoiceNo, PickNo
(Foreign) and GolferID (Foreign) that references GolferID in the Golfer
Table. I have the Choice Table as a Sub form in the Picks form. It works
great. My problem ... I need to find a way to assign a value to the ChoiceNo
in a query other than its ChoiceNo. Since you only pick 4 golfers I would
like each Golfer Selected in a Pick to have a Value of 1 to 4 based on when
selected. This would make it easier to create a cross tab query. Right now
since the ChoiceNo is auto Incremented it is difficult to create an organized
Cross Tab query. I would like my query to look like this. I gave the field a
name called Selected.

Name PickNo ChoiceNo Golfer Selected
Bob 3 9 Tiger Woods 1
Bob 3 10 David Duval 2
Bob 3 11 Davis Love 3
Bob 3 12 Jim Furyk 4
Joe 4 13 David Duval 1
Joe 4 14 Tom Watson 2
Joe 4 15 Tiger Woods 3
Joe 4 16 Fred Couples 4

I would probably make ChoiceNo Not visible. I just put it there to help make
what I need clearer. I tried making a counter with the Expression Builder for
the Selected field but had no luck. Any Suggestions.

Regards,

Chris

 

[Steve Schapel]

Chris,

On the Before Insert event of the Choice subform, you could write code
to assign the Selected value. If I understand you correctly, this code
might look like this...
Me.Selected = Nz(DMax("[Selected]","Choice","[PickNo]=" & Me.PickNo),0)+1