Hi Richard

Um, no, the guest handling is faulty.  

*Ignoring ties for now*, I think that what I need is to work out the
initial place as in the following pseudo code:

if NonGuest then RUNSUM(NonGuest) else RUNSUM(1) AS Place

That gives non-guests a rank ignoring the guests while the guests get
the full rank.

Then I need to add back the tie handling which *was* easy .. but if I
get more than one guest and one or more is tied with either a non-guest
or another guest.   Hmm, first ranked guest, tied or not, is 'normal'
but subsequent ones ... <reaching for headache pill>

.....

Hi Malcolm,

I do not understand how guests are ranked when they are equal with non-guests.

I thought that people were ranked according to their score with the addition rule that guests are ranked lower if they have an equal score with non-guests.
Is that right?

"FName"   "Score"   "NonGuest"   "Place"   "PlaceCount"   "NewPlace"
"Mary"   100   1   2   2   1
"Mary"   100   1   2   2   1
"Loreto"   96   1   3   1   3
"Roger"   28   1   4   1   4
"Fred"   25   1   5   1   5
"Jessica"   22   1   7   2   6
"Fiona"   22   1   7   2   6
"Ruth"   22   0   10   3   8 (Guest)
"Stuart"   22   0   10   3   8 (Guest)
"Daphne"   22   0   10   3   8 (Guest)
"James"   16   1   11   1   11
"Richard"   15   1   12   1   12
"John"   12   1   13   1   13
"John"   10   1   14   1   14
"Annette"   8   1   15   1   15

What should the rankings be in the above example?

Richard

Hi Richard

It is a bit weird and yet simple.

In essence, non-guests are ranked as if the guests were not there.
Your code manges that fine.
Guests are ranked overall, as if it was an Open event.
Your sample does not illustrate it well as you have all the guests tied
together.

The following example illustrates it better - I have simply changed
some guest states
I have added a runsum in the first column - helps to show the absolute
rank as if there were no ties.
(I have not edited the "Place" values)

#   "FName"   "Score"   "NonGuest"   "Place"   "PlaceCount"   "NewPlace"
1   "Mary"       100   1   2   2   1
2   "Mary"       100   1   2   2   1
3   "Loreto"      96   1   3   1   3
4   "Roger"      28   0   4   1   4 (Guest)
5   "Fred"      25   1   5   1   4 (absolute rank minus prior guests)
6   "Jessica"      22   1   7   2   5
7   "Fiona"      22   1   7   2   5
8   "Ruth"       22   0   10   3   6 (Guest) (absolute rank of first competitor
with this score)
9   "Stuart"      22   1   10   3   5
10  "Daphne"   22   0   10   3   6 (Guest)
11  "James"      16   1   11   1   8 (absolute rank minus prior guests)
12  "Richard"      15   1   12   1   9
13  "John"       12   0   13   1   13 (Guest)
14  "John"      10   1   14   1   10
15  "Annette"   8   1   15   1   11

I tried to implement it starting with your code and got quite close but
never managed to cover all the angles - my brain is not wired-up as
well as yours.

But I did get it to work on a two-pass version.  First I ran your code
(with minimal change) but selecting only the non-guests, and put its
final select into another view.  This would be all that was needed in
the absence of guests.

Then I repeated the run with all records, ignoring the guest
distinction.

Then I finally selected all the records from the non-guest run and
UNIONed that with a select of only the guests from the second run.
That did the business nicely and hurt my brain a lot less.

I then reviewed my navigational code and decided it did not look as bad
as I remembered it.  I then realised there were two more quirks I had
overlooked .. so I decided that if it ain't broke ... I would leave the
possible replacement to some future time.

An interesting exercise!
I hope I intrigued you rather than wasted your time.  

Malcolm

Hi Malcolm,

Great that you have it all under control.  These real world complications do make more intriguing and interesting challenges.

Richard

Richard Harding wrote:

> Hi Malcolm,
>
> Great that you have it all under control.  These real world
> complications do make more intriguing and interesting challenges.
>
> Richard

.... Too true.  :/