assigning "position" in repeat region

In fact, I was trying to simplify my question to get to the meat of it.

In reality, there are several territories, and within those territories, we
break down into smaller groups from which we pull winners from - categories.

So each Territory will have four 1st place winners - one from each of the
four categories.

As I said, I didn't mention it at first because I wanted to simplify - but
now that you're asking for more details, I thought I should mention it.



"Pizza Good" <PizzaGood@earthlink.com> wrote in message
news:esi89b$609$1@forums.macromedia.com...
> Hmm...interesting.
>
> In the sample data below, can you please sort them how you want them to
> appear? I want to see how you handle the Y's and N's.
>
>
> "HX" <mymail@somemail.com> wrote in message
> news:esi7jc$5ar$1@forums.macromedia.com...
>> Because "position" isn't a field in the database - it's determined based
>> on sorting the list on various criteria. For example, let's say the
>> winner for each territory will have met all of criteria_B and _C and then
>> will have the highest % in criteria_A . All I have in my database is:
>>
>> name territory Criteria_A Criteria_B Criteria_C
>> Joe 1 43% Y N
>> Jen 2 68% N N
>> Van 1 25% Y Y
>> Sue 2 56% N Y
>> Tim 1 42% Y Y
>> Len 2 66% N Y
>> Bob 1 74% Y Y
>> Ken 2 82% N N
>>
>> For Territory 1 I can pull out those names, sort based on my criteria,
>> then loop in my repeat region and assign their positions right there in
>> the asp.
>>
>> But when I combine ALL of them onto one page, I can't do that.
>>
>> Is there some way to set those positions in the sub-query (for each
>> territory) and reference that field in a 2nd query?
>>
>>
>>
>>
>> "Pizza Good" <PizzaGood@earthlink.com> wrote in message
>> news:esi3v9$1u4$1@forums.macromedia.com...
>>> Why not just sort by both fields?
>>>
>>> Not sure what your fields are called so I will use:
>>>
>>> territory
>>> position
>>>
>>> ORDER BY territory ASC, position ASC
>>>
>>>
>>>
>>>
>>> "HX" <mymail@somemail.com> wrote in message
>>> news:esi1bk$smt$1@forums.macromedia.com...
>>>>I have a site that runs a contest and displays winners from across the
>>>>nation. Through the year they compete and can go online to see where
>>>>they stand in relation to others in their same territory. In order to
>>>>display the winner's positions on the page, I sort the underlying data
>>>>in sql using the necessary criteria, then on my asp page, as part of the
>>>>repeat region, I run code that loops through the recordset and uses an
>>>>x+1 method of displaying each person's position. Whether or not this is
>>>>the best way to do this, it works.
>>>>
>>>> MY PROBLEM...
>>>>
>>>> Up until now I only needed to display the positions based on one
>>>> selected territory. So my results would always simply show 1 -x, in
>>>> order.
>>>>
>>>> Joe territory1 1
>>>> Van territory1 2
>>>> Tim territory1 3
>>>> Bob territory1 4
>>>>
>>>> NOW I am being asked to show the entire nation on one page for
>>>> administrators. They want to see ALL the territories and the positions
>>>> of each person for each territory, and have the ability to sort based
>>>> on various columns. So now my "positions" column will have several
>>>> people in 1st position, several in 2nd, etc.
>>>>
>>>> Joe territory1 1
>>>> Jen territory2 1
>>>> Van territory1 2
>>>> Sue territory2 2
>>>> Tim territory1 3
>>>> Len territory2 3
>>>> Bob territory1 4
>>>> Ken territory2 4
>>>>
>>>> I'm not sure how to go about this, and would greatly appreciate any
>>>> advice!
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

In the actual case the criteria for winning is that 1) ALL criteria are met
(there are actually four of them, not two as shown in my example), and then
2) you have the highest perentage.

So in the case below, remembering that each territory has its own set of
winners, it would look like this (if sorted by winning position - keeping in
mind that I need to allow sorting on ANY column by the user):

name territory Criteria_A Criteria_B Criteria_C POS
Bob 1 74% Y Y
1
Tim 1 42% Y Y
2
Van 1 25% Y Y
3
-----------------------------------------------------------------------
Len 2 66% N Y
1
Sue 2 56% N Y
2
Joe 1 43% Y N
4
-----------------------------------------------------------------------
Ken 2 82% N N
3
Jen 2 68% N N
4

Here you can see that Bob, Tim, and Van all have Y for both their B & C
criteria, so they are sorted to the top (how I do that is somewhat secondary
at this point -- you can just assume that Y=10 and N=0 and I'll sort on the
sum of those columns).

Then Len, Sue, and Joe each have one Y and one N, so they are grouped
together based on that, then sorted by percent. Note that Joe has a
position of 4 because he is in Territory 1.

Lastly, Ken and Jen have "N" for both criteria, so they are sorted last, and
then by their percentage.

I put a dashed line in there as a visual aid -- doesn't need to be in my
results.



"Pizza Good" <PizzaGood@earthlink.com> wrote in message
news:esi89b$609$1@forums.macromedia.com...
> Hmm...interesting.
>
> In the sample data below, can you please sort them how you want them to
> appear? I want to see how you handle the Y's and N's.
>
>
> "HX" <mymail@somemail.com> wrote in message
> news:esi7jc$5ar$1@forums.macromedia.com...
>> Because "position" isn't a field in the database - it's determined based
>> on sorting the list on various criteria. For example, let's say the
>> winner for each territory will have met all of criteria_B and _C and then
>> will have the highest % in criteria_A . All I have in my database is:
>>
>> name territory Criteria_A Criteria_B Criteria_C
>> Joe 1 43% Y N
>> Jen 2 68% N N
>> Van 1 25% Y Y
>> Sue 2 56% N Y
>> Tim 1 42% Y Y
>> Len 2 66% N Y
>> Bob 1 74% Y Y
>> Ken 2 82% N N
>>
>> For Territory 1 I can pull out those names, sort based on my criteria,
>> then loop in my repeat region and assign their positions right there in
>> the asp.
>>
>> But when I combine ALL of them onto one page, I can't do that.
>>
>> Is there some way to set those positions in the sub-query (for each
>> territory) and reference that field in a 2nd query?
>>
>>
>>
>>
>> "Pizza Good" <PizzaGood@earthlink.com> wrote in message
>> news:esi3v9$1u4$1@forums.macromedia.com...
>>> Why not just sort by both fields?
>>>
>>> Not sure what your fields are called so I will use:
>>>
>>> territory
>>> position
>>>
>>> ORDER BY territory ASC, position ASC
>>>
>>>
>>>
>>>
>>> "HX" <mymail@somemail.com> wrote in message
>>> news:esi1bk$smt$1@forums.macromedia.com...
>>>>I have a site that runs a contest and displays winners from across the
>>>>nation. Through the year they compete and can go online to see where
>>>>they stand in relation to others in their same territory. In order to
>>>>display the winner's positions on the page, I sort the underlying data
>>>>in sql using the necessary criteria, then on my asp page, as part of the
>>>>repeat region, I run code that loops through the recordset and uses an
>>>>x+1 method of displaying each person's position. Whether or not this is
>>>>the best way to do this, it works.
>>>>
>>>> MY PROBLEM...
>>>>
>>>> Up until now I only needed to display the positions based on one
>>>> selected territory. So my results would always simply show 1 -x, in
>>>> order.
>>>>
>>>> Joe territory1 1
>>>> Van territory1 2
>>>> Tim territory1 3
>>>> Bob territory1 4
>>>>
>>>> NOW I am being asked to show the entire nation on one page for
>>>> administrators. They want to see ALL the territories and the positions
>>>> of each person for each territory, and have the ability to sort based
>>>> on various columns. So now my "positions" column will have several
>>>> people in 1st position, several in 2nd, etc.
>>>>
>>>> Joe territory1 1
>>>> Jen territory2 1
>>>> Van territory1 2
>>>> Sue territory2 2
>>>> Tim territory1 3
>>>> Len territory2 3
>>>> Bob territory1 4
>>>> Ken territory2 4
>>>>
>>>> I'm not sure how to go about this, and would greatly appreciate any
>>>> advice!
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Hmm...interesting.

In the sample data below, can you please sort them how you want them to
appear? I want to see how you handle the Y's and N's.


"HX" <mymail@somemail.com> wrote in message
news:esi7jc$5ar$1@forums.macromedia.com...
> Because "position" isn't a field in the database - it's determined based
> on sorting the list on various criteria. For example, let's say the
> winner for each territory will have met all of criteria_B and _C and then
> will have the highest % in criteria_A . All I have in my database is:
>
> name territory Criteria_A Criteria_B Criteria_C
> Joe 1 43% Y N
> Jen 2 68% N N
> Van 1 25% Y Y
> Sue 2 56% N Y
> Tim 1 42% Y Y
> Len 2 66% N Y
> Bob 1 74% Y Y
> Ken 2 82% N N
>
> For Territory 1 I can pull out those names, sort based on my criteria,
> then loop in my repeat region and assign their positions right there in
> the asp.
>
> But when I combine ALL of them onto one page, I can't do that.
>
> Is there some way to set those positions in the sub-query (for each
> territory) and reference that field in a 2nd query?
>
>
>
>
> "Pizza Good" <PizzaGood@earthlink.com> wrote in message
> news:esi3v9$1u4$1@forums.macromedia.com...
>> Why not just sort by both fields?
>>
>> Not sure what your fields are called so I will use:
>>
>> territory
>> position
>>
>> ORDER BY territory ASC, position ASC
>>
>>
>>
>>
>> "HX" <mymail@somemail.com> wrote in message
>> news:esi1bk$smt$1@forums.macromedia.com...
>>>I have a site that runs a contest and displays winners from across the
>>>nation. Through the year they compete and can go online to see where
>>>they stand in relation to others in their same territory. In order to
>>>display the winner's positions on the page, I sort the underlying data in
>>>sql using the necessary criteria, then on my asp page, as part of the
>>>repeat region, I run code that loops through the recordset and uses an
>>>x+1 method of displaying each person's position. Whether or not this is
>>>the best way to do this, it works.
>>>
>>> MY PROBLEM...
>>>
>>> Up until now I only needed to display the positions based on one
>>> selected territory. So my results would always simply show 1 -x, in
>>> order.
>>>
>>> Joe territory1 1
>>> Van territory1 2
>>> Tim territory1 3
>>> Bob territory1 4
>>>
>>> NOW I am being asked to show the entire nation on one page for
>>> administrators. They want to see ALL the territories and the positions
>>> of each person for each territory, and have the ability to sort based on
>>> various columns. So now my "positions" column will have several people
>>> in 1st position, several in 2nd, etc.
>>>
>>> Joe territory1 1
>>> Jen territory2 1
>>> Van territory1 2
>>> Sue territory2 2
>>> Tim territory1 3
>>> Len territory2 3
>>> Bob territory1 4
>>> Ken territory2 4
>>>
>>> I'm not sure how to go about this, and would greatly appreciate any
>>> advice!
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>

Because "position" isn't a field in the database - it's determined based on
sorting the list on various criteria. For example, let's say the winner for
each territory will have met all of criteria_B and _C and then will have the
highest % in criteria_A . All I have in my database is:

name territory Criteria_A Criteria_B Criteria_C
Joe 1 43% Y N
Jen 2 68% N N
Van 1 25% Y Y
Sue 2 56% N Y
Tim 1 42% Y Y
Len 2 66% N Y
Bob 1 74% Y Y
Ken 2 82% N N

For Territory 1 I can pull out those names, sort based on my criteria, then
loop in my repeat region and assign their positions right there in the asp.

But when I combine ALL of them onto one page, I can't do that.

Is there some way to set those positions in the sub-query (for each
territory) and reference that field in a 2nd query?




"Pizza Good" <PizzaGood@earthlink.com> wrote in message
news:esi3v9$1u4$1@forums.macromedia.com...
> Why not just sort by both fields?
>
> Not sure what your fields are called so I will use:
>
> territory
> position
>
> ORDER BY territory ASC, position ASC
>
>
>
>
> "HX" <mymail@somemail.com> wrote in message
> news:esi1bk$smt$1@forums.macromedia.com...
>>I have a site that runs a contest and displays winners from across the
>>nation. Through the year they compete and can go online to see where they
>>stand in relation to others in their same territory. In order to display
>>the winner's positions on the page, I sort the underlying data in sql
>>using the necessary criteria, then on my asp page, as part of the repeat
>>region, I run code that loops through the recordset and uses an x+1 method
>>of displaying each person's position. Whether or not this is the best way
>>to do this, it works.
>>
>> MY PROBLEM...
>>
>> Up until now I only needed to display the positions based on one selected
>> territory. So my results would always simply show 1 -x, in order.
>>
>> Joe territory1 1
>> Van territory1 2
>> Tim territory1 3
>> Bob territory1 4
>>
>> NOW I am being asked to show the entire nation on one page for
>> administrators. They want to see ALL the territories and the positions
>> of each person for each territory, and have the ability to sort based on
>> various columns. So now my "positions" column will have several people
>> in 1st position, several in 2nd, etc.
>>
>> Joe territory1 1
>> Jen territory2 1
>> Van territory1 2
>> Sue territory2 2
>> Tim territory1 3
>> Len territory2 3
>> Bob territory1 4
>> Ken territory2 4
>>
>> I'm not sure how to go about this, and would greatly appreciate any
>> advice!
>>
>>
>>
>>
>>
>>
>>
>
>

Why not just sort by both fields?

Not sure what your fields are called so I will use:

territory
position

ORDER BY territory ASC, position ASC




"HX" <mymail@somemail.com> wrote in message
news:esi1bk$smt$1@forums.macromedia.com...
>I have a site that runs a contest and displays winners from across the
>nation. Through the year they compete and can go online to see where they
>stand in relation to others in their same territory. In order to display
>the winner's positions on the page, I sort the underlying data in sql using
>the necessary criteria, then on my asp page, as part of the repeat region,
>I run code that loops through the recordset and uses an x+1 method of
>displaying each person's position. Whether or not this is the best way to
>do this, it works.
>
> MY PROBLEM...
>
> Up until now I only needed to display the positions based on one selected
> territory. So my results would always simply show 1 -x, in order.
>
> Joe territory1 1
> Van territory1 2
> Tim territory1 3
> Bob territory1 4
>
> NOW I am being asked to show the entire nation on one page for
> administrators. They want to see ALL the territories and the positions of
> each person for each territory, and have the ability to sort based on
> various columns. So now my "positions" column will have several people in
> 1st position, several in 2nd, etc.
>
> Joe territory1 1
> Jen territory2 1
> Van territory1 2
> Sue territory2 2
> Tim territory1 3
> Len territory2 3
> Bob territory1 4
> Ken territory2 4
>
> I'm not sure how to go about this, and would greatly appreciate any
> advice!
>
>
>
>
>
>
>