Need to count Selections in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Back
Top