Complicated inner join??

Discussion in 'ASP General' started by Jeff, Dec 20, 2005.

  1. Jeff

    Jeff Guest

    Ok gang. Here is something complicated, well, at least to me anyway.
    Using Access DB

    I have a table in my DB called members. In that table, I have 2 tables I
    will be using "username" and "points"

    Now, I also have a table called all_matches. This table contains every match
    report. Over 25,000 of them. I have a "username" field an "outcome" field an
    "username1" field and "extra_match" field.

    What I need to do, is make a query that takes the username from table
    "members" and find :
    using the "username" field in the members table, i need to obtain stats
    that give me the following.

    1.) how many total matches played. Which would simply be a count of the
    username field in the all_matches table. Since people report the match win
    or lose.
    2.) how many wins, where extra_match = 0. This would be a count of username
    in the all_matches table where "outcome" = 'Win' and extra_match=0.
    3.) how many wins, where extra_match = 1. This would be a count of username
    in the all_matches table where "outcome" = 'Win' and extra_match=1
    4.) how many losses, where extra_match = 0. This would be a count of
    username in the all_matches table where "outcome" = 'Lose' and
    extra_match=0..
    5.) how many losses, where extra_match = 1. This would be a count of
    username in the all_matches table where "outcome" = 'Lose' and
    extra_match=1.
    6.) Then divide the wins into the total games and get a win %

    now I did this, using top 50 so it wasn't too intensive, since there are so
    many lines in the all_matches table. But what I can't do is get the sort
    right. But I think if i can do this in access, instead of doing it on an asp
    page, it will be easier.

    I had one that was done for me on another DB, but I was unable to modify it
    for this DB.
    I can show you the script of what I did on the asp page, but it is rather
    sloppy, and it doesn't work anyway. So I need help creating this SQL within
    access as a query.

    I know this is a lot,
    But any help would be appreciated. I know basic SQL, but I don't know inner
    and outer joins.
    THanks
    bam
    Jeff, Dec 20, 2005
    #1
    1. Advertising

  2. Jeff wrote:
    > Ok gang. Here is something complicated, well, at least to me anyway.
    > Using Access DB
    >
    > I have a table in my DB called members. In that table, I have 2
    > tables I will be using "username" and "points"


    Please include datatypes in your descriptions of your tables

    Table: Members
    Columns: username Text
    points Number? Long? Single?

    >
    > Now, I also have a table called all_matches. This table contains
    > every match report. Over 25,000 of them. I have a "username" field an
    > "outcome" field an "username1" field and "extra_match" field.


    Same deal. What is the purpose of "username1"?

    >
    > What I need to do, is make a query that takes the username from table
    > "members" and find :
    > using the "username" field in the members table, i need to obtain
    > stats that give me the following.
    >
    > 1.) how many total matches played. Which would simply be a count of
    > the username field in the all_matches table. Since people report the
    > match win or lose.


    SELECT mb.username, count(m.username) as TotalMatches
    FROM members as mb left join matches as m
    ON mb.username= m.username
    GROUP BY mb.username


    > 2.) how many wins, where extra_match = 0. This would be a count of
    > username in the all_matches table where "outcome" = 'Win' and
    > extra_match=0.


    <the FROM and GROUP clauses will not change, so I will not repeat then>

    SELECT mb.username, count(m.username) as TotalMatches,
    SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins


    > 3.) how many wins, where extra_match = 1. This would be a count of
    > username in the all_matches table where "outcome" = 'Win' and
    > extra_match=1


    SELECT mb.username, count(m.username) as TotalMatches,
    SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins



    > 4.) how many losses, where extra_match = 0. This would be a count of
    > username in the all_matches table where "outcome" = 'Lose' and
    > extra_match=0..


    SELECT mb.username, count(m.username) as TotalMatches,
    SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses


    > 5.) how many losses, where extra_match = 1. This would be a count of
    > username in the all_matches table where "outcome" = 'Lose' and
    > extra_match=1.


    SELECT mb.username, count(m.username) as TotalMatches,
    SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses


    > 6.) Then divide the wins into the total games and get a win %


    Is "wins" defined as TotalWins + TotalExtraWins? if so:
    SELECT mb.username, count(m.username) as TotalMatches,
    SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses,
    (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
    SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    count(m.username) as WinPercent
    <snip>
    > I know this is a lot,
    > But any help would be appreciated. I know basic SQL, but I don't know
    > inner and outer joins.


    :)
    That IS "basic SQL" ...
    It's the GROUP BY and the use of Iif that brings this into the "advanced"
    realm. :)

    Bob Barrows

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Dec 20, 2005
    #2
    1. Advertising

  3. Jeff

    Jeff Guest

    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > Jeff wrote:
    >> Ok gang. Here is something complicated, well, at least to me anyway.
    >> Using Access DB
    >>
    >> I have a table in my DB called members. In that table, I have 2
    >> tables I will be using "username" and "points"

    >
    > Please include datatypes in your descriptions of your tables
    >
    > Table: Members
    > Columns: username Text
    > points Number? Long? Single?

    ok. username is 55 ch text, points is long integer.

    >
    >>
    >> Now, I also have a table called all_matches. This table contains
    >> every match report. Over 25,000 of them. I have a "username" field an
    >> "outcome" field an "username1" field and "extra_match" field.

    >
    > Same deal. What is the purpose of "username1"?


    the purpose of username1 is that the person reporting is the username... the
    person he is reporting that he played is username1. I took over this, and
    haven't been able to sort everything out, and make it easier to read yet.

    >
    >>
    >> What I need to do, is make a query that takes the username from table
    >> "members" and find :
    >> using the "username" field in the members table, i need to obtain
    >> stats that give me the following.
    >>
    >> 1.) how many total matches played. Which would simply be a count of
    >> the username field in the all_matches table. Since people report the
    >> match win or lose.

    >
    > SELECT mb.username, count(m.username) as TotalMatches
    > FROM members as mb left join matches as m
    > ON mb.username= m.username
    > GROUP BY mb.username
    >
    >
    >> 2.) how many wins, where extra_match = 0. This would be a count of
    >> username in the all_matches table where "outcome" = 'Win' and
    >> extra_match=0.

    >
    > <the FROM and GROUP clauses will not change, so I will not repeat then>
    >
    > SELECT mb.username, count(m.username) as TotalMatches,
    > SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    >
    >
    >> 3.) how many wins, where extra_match = 1. This would be a count of
    >> username in the all_matches table where "outcome" = 'Win' and
    >> extra_match=1

    >
    > SELECT mb.username, count(m.username) as TotalMatches,
    > SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
    >
    >
    >
    >> 4.) how many losses, where extra_match = 0. This would be a count of
    >> username in the all_matches table where "outcome" = 'Lose' and
    >> extra_match=0..

    >
    > SELECT mb.username, count(m.username) as TotalMatches,
    > SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    > SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
    >
    >
    >> 5.) how many losses, where extra_match = 1. This would be a count of
    >> username in the all_matches table where "outcome" = 'Lose' and
    >> extra_match=1.

    >
    > SELECT mb.username, count(m.username) as TotalMatches,
    > SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    > SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    > SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses
    >
    >
    >> 6.) Then divide the wins into the total games and get a win %

    >
    > Is "wins" defined as TotalWins + TotalExtraWins? if so:


    yes, TotalWins would be the total from extra_match = 0 and extra_match = 1.

    > SELECT mb.username, count(m.username) as TotalMatches,
    > SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    > SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    > SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses,
    > (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
    > SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    > count(m.username) as WinPercent
    > <snip>
    >> I know this is a lot,
    >> But any help would be appreciated. I know basic SQL, but I don't know
    >> inner and outer joins.

    >
    > :)
    > That IS "basic SQL" ...
    > It's the GROUP BY and the use of Iif that brings this into the "advanced"
    > realm. :)
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >


    Ok, so I need to put all these into the sql in access. Hopefully nothing
    changed with the info I gave after your questions. In all actuality, the
    username1 shouldn't even come into play for this I don't think.
    Thanks Bob
    Jeff
    Jeff, Dec 20, 2005
    #3
  4. Jeff

    Jeff Guest

    Now if I wanted to sort by TotalWins, would I just add that in before the
    GROUP BY??


    "Bob Barrows [MVP]" <> wrote in message
    news:%...
    > Jeff wrote:
    >> Ok gang. Here is something complicated, well, at least to me anyway.
    >> Using Access DB
    >>
    >> I have a table in my DB called members. In that table, I have 2
    >> tables I will be using "username" and "points"

    >
    > Please include datatypes in your descriptions of your tables
    >
    > Table: Members
    > Columns: username Text
    > points Number? Long? Single?
    >
    >>
    >> Now, I also have a table called all_matches. This table contains
    >> every match report. Over 25,000 of them. I have a "username" field an
    >> "outcome" field an "username1" field and "extra_match" field.

    >
    > Same deal. What is the purpose of "username1"?
    >
    >>
    >> What I need to do, is make a query that takes the username from table
    >> "members" and find :
    >> using the "username" field in the members table, i need to obtain
    >> stats that give me the following.
    >>
    >> 1.) how many total matches played. Which would simply be a count of
    >> the username field in the all_matches table. Since people report the
    >> match win or lose.

    >
    > SELECT mb.username, count(m.username) as TotalMatches
    > FROM members as mb left join matches as m
    > ON mb.username= m.username
    > GROUP BY mb.username
    >
    >
    >> 2.) how many wins, where extra_match = 0. This would be a count of
    >> username in the all_matches table where "outcome" = 'Win' and
    >> extra_match=0.

    >
    > <the FROM and GROUP clauses will not change, so I will not repeat then>
    >
    > SELECT mb.username, count(m.username) as TotalMatches,
    > SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    >
    >
    >> 3.) how many wins, where extra_match = 1. This would be a count of
    >> username in the all_matches table where "outcome" = 'Win' and
    >> extra_match=1

    >
    > SELECT mb.username, count(m.username) as TotalMatches,
    > SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
    >
    >
    >
    >> 4.) how many losses, where extra_match = 0. This would be a count of
    >> username in the all_matches table where "outcome" = 'Lose' and
    >> extra_match=0..

    >
    > SELECT mb.username, count(m.username) as TotalMatches,
    > SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    > SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
    >
    >
    >> 5.) how many losses, where extra_match = 1. This would be a count of
    >> username in the all_matches table where "outcome" = 'Lose' and
    >> extra_match=1.

    >
    > SELECT mb.username, count(m.username) as TotalMatches,
    > SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    > SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    > SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses
    >
    >
    >> 6.) Then divide the wins into the total games and get a win %

    >
    > Is "wins" defined as TotalWins + TotalExtraWins? if so:
    > SELECT mb.username, count(m.username) as TotalMatches,
    > SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    > SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    > SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as TotalExtraLosses,
    > (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
    > SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    > count(m.username) as WinPercent
    > <snip>
    >> I know this is a lot,
    >> But any help would be appreciated. I know basic SQL, but I don't know
    >> inner and outer joins.

    >
    > :)
    > That IS "basic SQL" ...
    > It's the GROUP BY and the use of Iif that brings this into the "advanced"
    > realm. :)
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Jeff, Dec 20, 2005
    #4
  5. Jeff wrote:

    > Ok, so I need to put all these into the sql in access.


    Actually, a better plan would be to create a saved query in Access. Call it
    GetUserStats or something. In asp, simply call it like this:

    set cn=createobject("adodb.connection")
    cn.open "provider=microsoft.jet.oledb.4.0;data source=..."
    set rs=createobject("adodb.recordset")
    cn.GetUserStats rs
    'process the recordset

    > Hopefully
    > nothing changed with the info I gave after your questions.

    Nope

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Dec 20, 2005
    #5
  6. No. After. The ORDER BY always comes last. (The Access Query Builder allows
    you to answer these types of questions yourself)

    Jeff wrote:
    > Now if I wanted to sort by TotalWins, would I just add that in before
    > the GROUP BY??
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:%...
    >> Jeff wrote:
    >>> Ok gang. Here is something complicated, well, at least to me anyway.
    >>> Using Access DB
    >>>
    >>> I have a table in my DB called members. In that table, I have 2
    >>> tables I will be using "username" and "points"

    >>
    >> Please include datatypes in your descriptions of your tables
    >>
    >> Table: Members
    >> Columns: username Text
    >> points Number? Long? Single?
    >>
    >>>
    >>> Now, I also have a table called all_matches. This table contains
    >>> every match report. Over 25,000 of them. I have a "username" field
    >>> an "outcome" field an "username1" field and "extra_match" field.

    >>
    >> Same deal. What is the purpose of "username1"?
    >>
    >>>
    >>> What I need to do, is make a query that takes the username from
    >>> table "members" and find :
    >>> using the "username" field in the members table, i need to obtain
    >>> stats that give me the following.
    >>>
    >>> 1.) how many total matches played. Which would simply be a count of
    >>> the username field in the all_matches table. Since people report the
    >>> match win or lose.

    >>
    >> SELECT mb.username, count(m.username) as TotalMatches
    >> FROM members as mb left join matches as m
    >> ON mb.username= m.username
    >> GROUP BY mb.username
    >>
    >>
    >>> 2.) how many wins, where extra_match = 0. This would be a count of
    >>> username in the all_matches table where "outcome" = 'Win' and
    >>> extra_match=0.

    >>
    >> <the FROM and GROUP clauses will not change, so I will not repeat
    >> then>
    >>
    >> SELECT mb.username, count(m.username) as TotalMatches,
    >> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    >>
    >>
    >>> 3.) how many wins, where extra_match = 1. This would be a count of
    >>> username in the all_matches table where "outcome" = 'Win' and
    >>> extra_match=1

    >>
    >> SELECT mb.username, count(m.username) as TotalMatches,
    >> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
    >>
    >>
    >>
    >>> 4.) how many losses, where extra_match = 0. This would be a count of
    >>> username in the all_matches table where "outcome" = 'Lose' and
    >>> extra_match=0..

    >>
    >> SELECT mb.username, count(m.username) as TotalMatches,
    >> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
    >>
    >>
    >>> 5.) how many losses, where extra_match = 1. This would be a count of
    >>> username in the all_matches table where "outcome" = 'Lose' and
    >>> extra_match=1.

    >>
    >> SELECT mb.username, count(m.username) as TotalMatches,
    >> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >> TotalExtraLosses
    >>
    >>
    >>> 6.) Then divide the wins into the total games and get a win %

    >>
    >> Is "wins" defined as TotalWins + TotalExtraWins? if so:
    >> SELECT mb.username, count(m.username) as TotalMatches,
    >> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
    >> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    >> count(m.username) as WinPercent
    >> <snip>
    >>> I know this is a lot,
    >>> But any help would be appreciated. I know basic SQL, but I don't
    >>> know inner and outer joins.

    >>
    >> :)
    >> That IS "basic SQL" ...
    >> It's the GROUP BY and the use of Iif that brings this into the
    >> "advanced" realm. :)
    >>
    >> Bob Barrows
    >>
    >> --
    >> Microsoft MVP -- ASP/ASP.NET
    >> Please reply to the newsgroup. The email account listed in my From
    >> header is my spam trap, so I don't check it very often. You will get
    >> a quicker response by posting to the newsgroup.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Dec 20, 2005
    #6
  7. Jeff

    Jeff Guest

    Thanks Bob, I actually did a build in access with this query, then call it
    from the asp page.
    YOUDAMAN!!



    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > No. After. The ORDER BY always comes last. (The Access Query Builder
    > allows
    > you to answer these types of questions yourself)
    >
    > Jeff wrote:
    >> Now if I wanted to sort by TotalWins, would I just add that in before
    >> the GROUP BY??
    >>
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:%...
    >>> Jeff wrote:
    >>>> Ok gang. Here is something complicated, well, at least to me anyway.
    >>>> Using Access DB
    >>>>
    >>>> I have a table in my DB called members. In that table, I have 2
    >>>> tables I will be using "username" and "points"
    >>>
    >>> Please include datatypes in your descriptions of your tables
    >>>
    >>> Table: Members
    >>> Columns: username Text
    >>> points Number? Long? Single?
    >>>
    >>>>
    >>>> Now, I also have a table called all_matches. This table contains
    >>>> every match report. Over 25,000 of them. I have a "username" field
    >>>> an "outcome" field an "username1" field and "extra_match" field.
    >>>
    >>> Same deal. What is the purpose of "username1"?
    >>>
    >>>>
    >>>> What I need to do, is make a query that takes the username from
    >>>> table "members" and find :
    >>>> using the "username" field in the members table, i need to obtain
    >>>> stats that give me the following.
    >>>>
    >>>> 1.) how many total matches played. Which would simply be a count of
    >>>> the username field in the all_matches table. Since people report the
    >>>> match win or lose.
    >>>
    >>> SELECT mb.username, count(m.username) as TotalMatches
    >>> FROM members as mb left join matches as m
    >>> ON mb.username= m.username
    >>> GROUP BY mb.username
    >>>
    >>>
    >>>> 2.) how many wins, where extra_match = 0. This would be a count of
    >>>> username in the all_matches table where "outcome" = 'Win' and
    >>>> extra_match=0.
    >>>
    >>> <the FROM and GROUP clauses will not change, so I will not repeat
    >>> then>
    >>>
    >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    >>>
    >>>
    >>>> 3.) how many wins, where extra_match = 1. This would be a count of
    >>>> username in the all_matches table where "outcome" = 'Win' and
    >>>> extra_match=1
    >>>
    >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
    >>>
    >>>
    >>>
    >>>> 4.) how many losses, where extra_match = 0. This would be a count of
    >>>> username in the all_matches table where "outcome" = 'Lose' and
    >>>> extra_match=0..
    >>>
    >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
    >>>
    >>>
    >>>> 5.) how many losses, where extra_match = 1. This would be a count of
    >>>> username in the all_matches table where "outcome" = 'Lose' and
    >>>> extra_match=1.
    >>>
    >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >>> TotalExtraLosses
    >>>
    >>>
    >>>> 6.) Then divide the wins into the total games and get a win %
    >>>
    >>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
    >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
    >>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    >>> count(m.username) as WinPercent
    >>> <snip>
    >>>> I know this is a lot,
    >>>> But any help would be appreciated. I know basic SQL, but I don't
    >>>> know inner and outer joins.
    >>>
    >>> :)
    >>> That IS "basic SQL" ...
    >>> It's the GROUP BY and the use of Iif that brings this into the
    >>> "advanced" realm. :)
    >>>
    >>> Bob Barrows
    >>>
    >>> --
    >>> Microsoft MVP -- ASP/ASP.NET
    >>> Please reply to the newsgroup. The email account listed in my From
    >>> header is my spam trap, so I don't check it very often. You will get
    >>> a quicker response by posting to the newsgroup.

    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Jeff, Dec 20, 2005
    #7
  8. Jeff

    Jeff Guest

    Ok. Let me show you what I have now. Here is my script on the asp page:
    <%
    set admin5 = conn.execute("select top 50 * from final order by TotalWins
    DESC")

    numRealPos = 0
    numDisplayPos = 1
    do while not admin5.eof
    var1 = admin5.fields.item("username").value
    var2 = admin5.fields.item("TotalMatches").value
    var3 = admin5.fields.item("TotalWins").value
    var4 = admin5.fields.item("TotalExtraWIns").value
    var5 = admin5.fields.item("TotalLosses").value
    var6 = admin5.fields.item("TotalExtraLosses").value
    var7 = admin5.fields.item("WinPercent").value

    var8 = var3 + var4
    var9 = var5 + var6

    var10 = var3 * 5 ''total win points
    var11 = var5 * 2 ''total losses points
    var11a = var4 + var6 '' Total Extra Matches
    var12 = var10 + var11 + var11a '' total points
    var13 = (var8/var2) * 100
    var14 = formatnumber(var13,0)

    numRealPos = numRealPos + 1
    If Not var1 = numLastValue Then
    numLastValue = var3
    numDisplayPos = numRealPos
    End If

    %>

    <tr>
    <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    solid; border-width: 1px"><b><%=numDisplayPos%>&nbsp;</b></td>
    <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    solid; border-width: 1px"><b><%=var1%>&nbsp;</b></td>
    <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    solid; border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td>
    <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
    <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    solid; border-width: 1px"><b><%=var12%>&nbsp;</b></td>
    </tr>
    <%
    admin5.movenext
    loop
    %>
    Now it starts off sorting correct, but 2 things appear to be working wrong.
    First, it is displaying 51 records. Second, down around Rank 32, it starts
    going off sort.
    http://www.logotour.com/tour_rankings.asp
    check out what I mean. I tried to put the order clause in the SQL in Access,
    but then it wouldn't work. It prompted me for a Parameter.
    Any Ideas??
    Jeff


    Now the problem is, the total
    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > No. After. The ORDER BY always comes last. (The Access Query Builder
    > allows
    > you to answer these types of questions yourself)
    >
    > Jeff wrote:
    >> Now if I wanted to sort by TotalWins, would I just add that in before
    >> the GROUP BY??
    >>
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:%...
    >>> Jeff wrote:
    >>>> Ok gang. Here is something complicated, well, at least to me anyway.
    >>>> Using Access DB
    >>>>
    >>>> I have a table in my DB called members. In that table, I have 2
    >>>> tables I will be using "username" and "points"
    >>>
    >>> Please include datatypes in your descriptions of your tables
    >>>
    >>> Table: Members
    >>> Columns: username Text
    >>> points Number? Long? Single?
    >>>
    >>>>
    >>>> Now, I also have a table called all_matches. This table contains
    >>>> every match report. Over 25,000 of them. I have a "username" field
    >>>> an "outcome" field an "username1" field and "extra_match" field.
    >>>
    >>> Same deal. What is the purpose of "username1"?
    >>>
    >>>>
    >>>> What I need to do, is make a query that takes the username from
    >>>> table "members" and find :
    >>>> using the "username" field in the members table, i need to obtain
    >>>> stats that give me the following.
    >>>>
    >>>> 1.) how many total matches played. Which would simply be a count of
    >>>> the username field in the all_matches table. Since people report the
    >>>> match win or lose.
    >>>
    >>> SELECT mb.username, count(m.username) as TotalMatches
    >>> FROM members as mb left join matches as m
    >>> ON mb.username= m.username
    >>> GROUP BY mb.username
    >>>
    >>>
    >>>> 2.) how many wins, where extra_match = 0. This would be a count of
    >>>> username in the all_matches table where "outcome" = 'Win' and
    >>>> extra_match=0.
    >>>
    >>> <the FROM and GROUP clauses will not change, so I will not repeat
    >>> then>
    >>>
    >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    >>>
    >>>
    >>>> 3.) how many wins, where extra_match = 1. This would be a count of
    >>>> username in the all_matches table where "outcome" = 'Win' and
    >>>> extra_match=1
    >>>
    >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
    >>>
    >>>
    >>>
    >>>> 4.) how many losses, where extra_match = 0. This would be a count of
    >>>> username in the all_matches table where "outcome" = 'Lose' and
    >>>> extra_match=0..
    >>>
    >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
    >>>
    >>>
    >>>> 5.) how many losses, where extra_match = 1. This would be a count of
    >>>> username in the all_matches table where "outcome" = 'Lose' and
    >>>> extra_match=1.
    >>>
    >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >>> TotalExtraLosses
    >>>
    >>>
    >>>> 6.) Then divide the wins into the total games and get a win %
    >>>
    >>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
    >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
    >>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    >>> count(m.username) as WinPercent
    >>> <snip>
    >>>> I know this is a lot,
    >>>> But any help would be appreciated. I know basic SQL, but I don't
    >>>> know inner and outer joins.
    >>>
    >>> :)
    >>> That IS "basic SQL" ...
    >>> It's the GROUP BY and the use of Iif that brings this into the
    >>> "advanced" realm. :)
    >>>
    >>> Bob Barrows
    >>>
    >>> --
    >>> Microsoft MVP -- ASP/ASP.NET
    >>> Please reply to the newsgroup. The email account listed in my From
    >>> header is my spam trap, so I don't check it very often. You will get
    >>> a quicker response by posting to the newsgroup.

    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Jeff, Dec 20, 2005
    #8
  9. Jeff

    Bob Lehmann Guest

    >> First, it is displaying 51 records.
    That is because you have a tie somewhere. For example, if you had
    1,2,3,3,4,5 as values, and selected top 5, you would get back 6 rows.

    >> It prompted me for a Parameter.

    Is "TotalWins" a column in your table?

    PS - Quit using "*" for your SELECT. Explicitly name the columns you want
    returned.

    Bob Lehmann

    "Jeff" <> wrote in message
    news:...
    > Ok. Let me show you what I have now. Here is my script on the asp page:
    > <%
    > set admin5 = conn.execute("select top 50 * from final order by TotalWins
    > DESC")
    >
    > numRealPos = 0
    > numDisplayPos = 1
    > do while not admin5.eof
    > var1 = admin5.fields.item("username").value
    > var2 = admin5.fields.item("TotalMatches").value
    > var3 = admin5.fields.item("TotalWins").value
    > var4 = admin5.fields.item("TotalExtraWIns").value
    > var5 = admin5.fields.item("TotalLosses").value
    > var6 = admin5.fields.item("TotalExtraLosses").value
    > var7 = admin5.fields.item("WinPercent").value
    >
    > var8 = var3 + var4
    > var9 = var5 + var6
    >
    > var10 = var3 * 5 ''total win points
    > var11 = var5 * 2 ''total losses points
    > var11a = var4 + var6 '' Total Extra Matches
    > var12 = var10 + var11 + var11a '' total points
    > var13 = (var8/var2) * 100
    > var14 = formatnumber(var13,0)
    >
    > numRealPos = numRealPos + 1
    > If Not var1 = numLastValue Then
    > numLastValue = var3
    > numDisplayPos = numRealPos
    > End If
    >
    > %>
    >
    > <tr>
    > <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    > solid; border-width: 1px"><b><%=numDisplayPos%>&nbsp;</b></td>
    > <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    > solid; border-width: 1px"><b><%=var1%>&nbsp;</b></td>
    > <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    > solid; border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td>
    > <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    > solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
    > <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    > solid; border-width: 1px"><b><%=var12%>&nbsp;</b></td>
    > </tr>
    > <%
    > admin5.movenext
    > loop
    > %>
    > Now it starts off sorting correct, but 2 things appear to be working

    wrong.
    > First, it is displaying 51 records. Second, down around Rank 32, it starts
    > going off sort.
    > http://www.logotour.com/tour_rankings.asp
    > check out what I mean. I tried to put the order clause in the SQL in

    Access,
    > but then it wouldn't work. It prompted me for a Parameter.
    > Any Ideas??
    > Jeff
    >
    >
    > Now the problem is, the total
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    > > No. After. The ORDER BY always comes last. (The Access Query Builder
    > > allows
    > > you to answer these types of questions yourself)
    > >
    > > Jeff wrote:
    > >> Now if I wanted to sort by TotalWins, would I just add that in before
    > >> the GROUP BY??
    > >>
    > >>
    > >> "Bob Barrows [MVP]" <> wrote in message
    > >> news:%...
    > >>> Jeff wrote:
    > >>>> Ok gang. Here is something complicated, well, at least to me anyway.
    > >>>> Using Access DB
    > >>>>
    > >>>> I have a table in my DB called members. In that table, I have 2
    > >>>> tables I will be using "username" and "points"
    > >>>
    > >>> Please include datatypes in your descriptions of your tables
    > >>>
    > >>> Table: Members
    > >>> Columns: username Text
    > >>> points Number? Long? Single?
    > >>>
    > >>>>
    > >>>> Now, I also have a table called all_matches. This table contains
    > >>>> every match report. Over 25,000 of them. I have a "username" field
    > >>>> an "outcome" field an "username1" field and "extra_match" field.
    > >>>
    > >>> Same deal. What is the purpose of "username1"?
    > >>>
    > >>>>
    > >>>> What I need to do, is make a query that takes the username from
    > >>>> table "members" and find :
    > >>>> using the "username" field in the members table, i need to obtain
    > >>>> stats that give me the following.
    > >>>>
    > >>>> 1.) how many total matches played. Which would simply be a count of
    > >>>> the username field in the all_matches table. Since people report the
    > >>>> match win or lose.
    > >>>
    > >>> SELECT mb.username, count(m.username) as TotalMatches
    > >>> FROM members as mb left join matches as m
    > >>> ON mb.username= m.username
    > >>> GROUP BY mb.username
    > >>>
    > >>>
    > >>>> 2.) how many wins, where extra_match = 0. This would be a count of
    > >>>> username in the all_matches table where "outcome" = 'Win' and
    > >>>> extra_match=0.
    > >>>
    > >>> <the FROM and GROUP clauses will not change, so I will not repeat
    > >>> then>
    > >>>
    > >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    > >>>
    > >>>
    > >>>> 3.) how many wins, where extra_match = 1. This would be a count of
    > >>>> username in the all_matches table where "outcome" = 'Win' and
    > >>>> extra_match=1
    > >>>
    > >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
    > >>>
    > >>>
    > >>>
    > >>>> 4.) how many losses, where extra_match = 0. This would be a count of
    > >>>> username in the all_matches table where "outcome" = 'Lose' and
    > >>>> extra_match=0..
    > >>>
    > >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    > >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
    > >>>
    > >>>
    > >>>> 5.) how many losses, where extra_match = 1. This would be a count of
    > >>>> username in the all_matches table where "outcome" = 'Lose' and
    > >>>> extra_match=1.
    > >>>
    > >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    > >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    > >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    > >>> TotalExtraLosses
    > >>>
    > >>>
    > >>>> 6.) Then divide the wins into the total games and get a win %
    > >>>
    > >>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
    > >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    > >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    > >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    > >>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
    > >>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    > >>> count(m.username) as WinPercent
    > >>> <snip>
    > >>>> I know this is a lot,
    > >>>> But any help would be appreciated. I know basic SQL, but I don't
    > >>>> know inner and outer joins.
    > >>>
    > >>> :)
    > >>> That IS "basic SQL" ...
    > >>> It's the GROUP BY and the use of Iif that brings this into the
    > >>> "advanced" realm. :)
    > >>>
    > >>> Bob Barrows
    > >>>
    > >>> --
    > >>> Microsoft MVP -- ASP/ASP.NET
    > >>> Please reply to the newsgroup. The email account listed in my From
    > >>> header is my spam trap, so I don't check it very often. You will get
    > >>> a quicker response by posting to the newsgroup.

    > >
    > > --
    > > Microsoft MVP -- ASP/ASP.NET
    > > Please reply to the newsgroup. The email account listed in my From
    > > header is my spam trap, so I don't check it very often. You will get a
    > > quicker response by posting to the newsgroup.
    > >
    > >

    >
    >
    Bob Lehmann, Dec 21, 2005
    #9
  10. Jeff

    Jeff Guest

    "Bob Lehmann" <> wrote in message
    news:...
    >>> First, it is displaying 51 records.

    > That is because you have a tie somewhere. For example, if you had
    > 1,2,3,3,4,5 as values, and selected top 5, you would get back 6 rows.


    I thought that might be the reason actually.
    >
    >>> It prompted me for a Parameter.

    > Is "TotalWins" a column in your table?

    Yes, TotalWins IS a field.
    >
    > PS - Quit using "*" for your SELECT. Explicitly name the columns you want

    I am doing that because I do want all fields that are created in the SQL
    that is in access. Is this still wrong??

    > returned.
    >
    > Bob Lehmann
    >
    > "Jeff" <> wrote in message
    > news:...
    >> Ok. Let me show you what I have now. Here is my script on the asp page:
    >> <%
    >> set admin5 = conn.execute("select top 50 * from final order by TotalWins
    >> DESC")
    >>
    >> numRealPos = 0
    >> numDisplayPos = 1
    >> do while not admin5.eof
    >> var1 = admin5.fields.item("username").value
    >> var2 = admin5.fields.item("TotalMatches").value
    >> var3 = admin5.fields.item("TotalWins").value
    >> var4 = admin5.fields.item("TotalExtraWIns").value
    >> var5 = admin5.fields.item("TotalLosses").value
    >> var6 = admin5.fields.item("TotalExtraLosses").value
    >> var7 = admin5.fields.item("WinPercent").value
    >>
    >> var8 = var3 + var4
    >> var9 = var5 + var6
    >>
    >> var10 = var3 * 5 ''total win points
    >> var11 = var5 * 2 ''total losses points
    >> var11a = var4 + var6 '' Total Extra Matches
    >> var12 = var10 + var11 + var11a '' total points
    >> var13 = (var8/var2) * 100
    >> var14 = formatnumber(var13,0)
    >>
    >> numRealPos = numRealPos + 1
    >> If Not var1 = numLastValue Then
    >> numLastValue = var3
    >> numDisplayPos = numRealPos
    >> End If
    >>
    >> %>
    >>
    >> <tr>
    >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >> solid; border-width: 1px"><b><%=numDisplayPos%>&nbsp;</b></td>
    >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >> solid; border-width: 1px"><b><%=var1%>&nbsp;</b></td>
    >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >> solid; border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td>
    >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >> solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
    >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >> solid; border-width: 1px"><b><%=var12%>&nbsp;</b></td>
    >> </tr>
    >> <%
    >> admin5.movenext
    >> loop
    >> %>
    >> Now it starts off sorting correct, but 2 things appear to be working

    > wrong.
    >> First, it is displaying 51 records. Second, down around Rank 32, it
    >> starts
    >> going off sort.
    >> http://www.logotour.com/tour_rankings.asp
    >> check out what I mean. I tried to put the order clause in the SQL in

    > Access,
    >> but then it wouldn't work. It prompted me for a Parameter.
    >> Any Ideas??
    >> Jeff
    >>
    >>
    >> Now the problem is, the total
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:...
    >> > No. After. The ORDER BY always comes last. (The Access Query Builder
    >> > allows
    >> > you to answer these types of questions yourself)
    >> >
    >> > Jeff wrote:
    >> >> Now if I wanted to sort by TotalWins, would I just add that in before
    >> >> the GROUP BY??
    >> >>
    >> >>
    >> >> "Bob Barrows [MVP]" <> wrote in message
    >> >> news:%...
    >> >>> Jeff wrote:
    >> >>>> Ok gang. Here is something complicated, well, at least to me anyway.
    >> >>>> Using Access DB
    >> >>>>
    >> >>>> I have a table in my DB called members. In that table, I have 2
    >> >>>> tables I will be using "username" and "points"
    >> >>>
    >> >>> Please include datatypes in your descriptions of your tables
    >> >>>
    >> >>> Table: Members
    >> >>> Columns: username Text
    >> >>> points Number? Long? Single?
    >> >>>
    >> >>>>
    >> >>>> Now, I also have a table called all_matches. This table contains
    >> >>>> every match report. Over 25,000 of them. I have a "username" field
    >> >>>> an "outcome" field an "username1" field and "extra_match" field.
    >> >>>
    >> >>> Same deal. What is the purpose of "username1"?
    >> >>>
    >> >>>>
    >> >>>> What I need to do, is make a query that takes the username from
    >> >>>> table "members" and find :
    >> >>>> using the "username" field in the members table, i need to obtain
    >> >>>> stats that give me the following.
    >> >>>>
    >> >>>> 1.) how many total matches played. Which would simply be a count of
    >> >>>> the username field in the all_matches table. Since people report the
    >> >>>> match win or lose.
    >> >>>
    >> >>> SELECT mb.username, count(m.username) as TotalMatches
    >> >>> FROM members as mb left join matches as m
    >> >>> ON mb.username= m.username
    >> >>> GROUP BY mb.username
    >> >>>
    >> >>>
    >> >>>> 2.) how many wins, where extra_match = 0. This would be a count of
    >> >>>> username in the all_matches table where "outcome" = 'Win' and
    >> >>>> extra_match=0.
    >> >>>
    >> >>> <the FROM and GROUP clauses will not change, so I will not repeat
    >> >>> then>
    >> >>>
    >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    >> >>>
    >> >>>
    >> >>>> 3.) how many wins, where extra_match = 1. This would be a count of
    >> >>>> username in the all_matches table where "outcome" = 'Win' and
    >> >>>> extra_match=1
    >> >>>
    >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
    >> >>>
    >> >>>
    >> >>>
    >> >>>> 4.) how many losses, where extra_match = 0. This would be a count of
    >> >>>> username in the all_matches table where "outcome" = 'Lose' and
    >> >>>> extra_match=0..
    >> >>>
    >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
    >> >>>
    >> >>>
    >> >>>> 5.) how many losses, where extra_match = 1. This would be a count of
    >> >>>> username in the all_matches table where "outcome" = 'Lose' and
    >> >>>> extra_match=1.
    >> >>>
    >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >> >>> TotalExtraLosses
    >> >>>
    >> >>>
    >> >>>> 6.) Then divide the wins into the total games and get a win %
    >> >>>
    >> >>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
    >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >> >>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
    >> >>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    >> >>> count(m.username) as WinPercent
    >> >>> <snip>
    >> >>>> I know this is a lot,
    >> >>>> But any help would be appreciated. I know basic SQL, but I don't
    >> >>>> know inner and outer joins.
    >> >>>
    >> >>> :)
    >> >>> That IS "basic SQL" ...
    >> >>> It's the GROUP BY and the use of Iif that brings this into the
    >> >>> "advanced" realm. :)
    >> >>>
    >> >>> Bob Barrows
    >> >>>
    >> >>> --
    >> >>> Microsoft MVP -- ASP/ASP.NET
    >> >>> Please reply to the newsgroup. The email account listed in my From
    >> >>> header is my spam trap, so I don't check it very often. You will get
    >> >>> a quicker response by posting to the newsgroup.
    >> >
    >> > --
    >> > Microsoft MVP -- ASP/ASP.NET
    >> > Please reply to the newsgroup. The email account listed in my From
    >> > header is my spam trap, so I don't check it very often. You will get a
    >> > quicker response by posting to the newsgroup.
    >> >
    >> >

    >>
    >>

    >
    >
    Jeff, Dec 21, 2005
    #10
  11. Jeff

    Jeff Guest

    But this still Doesn't answer why it is going out of sort 2/3 of the way
    down, does it??

    Jeff


    "Bob Lehmann" <> wrote in message
    news:...
    >>> First, it is displaying 51 records.

    > That is because you have a tie somewhere. For example, if you had
    > 1,2,3,3,4,5 as values, and selected top 5, you would get back 6 rows.
    >
    >>> It prompted me for a Parameter.

    > Is "TotalWins" a column in your table?
    >
    > PS - Quit using "*" for your SELECT. Explicitly name the columns you want
    > returned.
    >
    > Bob Lehmann
    >
    > "Jeff" <> wrote in message
    > news:...
    >> Ok. Let me show you what I have now. Here is my script on the asp page:
    >> <%
    >> set admin5 = conn.execute("select top 50 * from final order by TotalWins
    >> DESC")
    >>
    >> numRealPos = 0
    >> numDisplayPos = 1
    >> do while not admin5.eof
    >> var1 = admin5.fields.item("username").value
    >> var2 = admin5.fields.item("TotalMatches").value
    >> var3 = admin5.fields.item("TotalWins").value
    >> var4 = admin5.fields.item("TotalExtraWIns").value
    >> var5 = admin5.fields.item("TotalLosses").value
    >> var6 = admin5.fields.item("TotalExtraLosses").value
    >> var7 = admin5.fields.item("WinPercent").value
    >>
    >> var8 = var3 + var4
    >> var9 = var5 + var6
    >>
    >> var10 = var3 * 5 ''total win points
    >> var11 = var5 * 2 ''total losses points
    >> var11a = var4 + var6 '' Total Extra Matches
    >> var12 = var10 + var11 + var11a '' total points
    >> var13 = (var8/var2) * 100
    >> var14 = formatnumber(var13,0)
    >>
    >> numRealPos = numRealPos + 1
    >> If Not var1 = numLastValue Then
    >> numLastValue = var3
    >> numDisplayPos = numRealPos
    >> End If
    >>
    >> %>
    >>
    >> <tr>
    >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >> solid; border-width: 1px"><b><%=numDisplayPos%>&nbsp;</b></td>
    >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >> solid; border-width: 1px"><b><%=var1%>&nbsp;</b></td>
    >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >> solid; border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td>
    >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >> solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
    >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >> solid; border-width: 1px"><b><%=var12%>&nbsp;</b></td>
    >> </tr>
    >> <%
    >> admin5.movenext
    >> loop
    >> %>
    >> Now it starts off sorting correct, but 2 things appear to be working

    > wrong.
    >> First, it is displaying 51 records. Second, down around Rank 32, it
    >> starts
    >> going off sort.
    >> http://www.logotour.com/tour_rankings.asp
    >> check out what I mean. I tried to put the order clause in the SQL in

    > Access,
    >> but then it wouldn't work. It prompted me for a Parameter.
    >> Any Ideas??
    >> Jeff
    >>
    >>
    >> Now the problem is, the total
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:...
    >> > No. After. The ORDER BY always comes last. (The Access Query Builder
    >> > allows
    >> > you to answer these types of questions yourself)
    >> >
    >> > Jeff wrote:
    >> >> Now if I wanted to sort by TotalWins, would I just add that in before
    >> >> the GROUP BY??
    >> >>
    >> >>
    >> >> "Bob Barrows [MVP]" <> wrote in message
    >> >> news:%...
    >> >>> Jeff wrote:
    >> >>>> Ok gang. Here is something complicated, well, at least to me anyway.
    >> >>>> Using Access DB
    >> >>>>
    >> >>>> I have a table in my DB called members. In that table, I have 2
    >> >>>> tables I will be using "username" and "points"
    >> >>>
    >> >>> Please include datatypes in your descriptions of your tables
    >> >>>
    >> >>> Table: Members
    >> >>> Columns: username Text
    >> >>> points Number? Long? Single?
    >> >>>
    >> >>>>
    >> >>>> Now, I also have a table called all_matches. This table contains
    >> >>>> every match report. Over 25,000 of them. I have a "username" field
    >> >>>> an "outcome" field an "username1" field and "extra_match" field.
    >> >>>
    >> >>> Same deal. What is the purpose of "username1"?
    >> >>>
    >> >>>>
    >> >>>> What I need to do, is make a query that takes the username from
    >> >>>> table "members" and find :
    >> >>>> using the "username" field in the members table, i need to obtain
    >> >>>> stats that give me the following.
    >> >>>>
    >> >>>> 1.) how many total matches played. Which would simply be a count of
    >> >>>> the username field in the all_matches table. Since people report the
    >> >>>> match win or lose.
    >> >>>
    >> >>> SELECT mb.username, count(m.username) as TotalMatches
    >> >>> FROM members as mb left join matches as m
    >> >>> ON mb.username= m.username
    >> >>> GROUP BY mb.username
    >> >>>
    >> >>>
    >> >>>> 2.) how many wins, where extra_match = 0. This would be a count of
    >> >>>> username in the all_matches table where "outcome" = 'Win' and
    >> >>>> extra_match=0.
    >> >>>
    >> >>> <the FROM and GROUP clauses will not change, so I will not repeat
    >> >>> then>
    >> >>>
    >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    >> >>>
    >> >>>
    >> >>>> 3.) how many wins, where extra_match = 1. This would be a count of
    >> >>>> username in the all_matches table where "outcome" = 'Win' and
    >> >>>> extra_match=1
    >> >>>
    >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
    >> >>>
    >> >>>
    >> >>>
    >> >>>> 4.) how many losses, where extra_match = 0. This would be a count of
    >> >>>> username in the all_matches table where "outcome" = 'Lose' and
    >> >>>> extra_match=0..
    >> >>>
    >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
    >> >>>
    >> >>>
    >> >>>> 5.) how many losses, where extra_match = 1. This would be a count of
    >> >>>> username in the all_matches table where "outcome" = 'Lose' and
    >> >>>> extra_match=1.
    >> >>>
    >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >> >>> TotalExtraLosses
    >> >>>
    >> >>>
    >> >>>> 6.) Then divide the wins into the total games and get a win %
    >> >>>
    >> >>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
    >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >> >>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
    >> >>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    >> >>> count(m.username) as WinPercent
    >> >>> <snip>
    >> >>>> I know this is a lot,
    >> >>>> But any help would be appreciated. I know basic SQL, but I don't
    >> >>>> know inner and outer joins.
    >> >>>
    >> >>> :)
    >> >>> That IS "basic SQL" ...
    >> >>> It's the GROUP BY and the use of Iif that brings this into the
    >> >>> "advanced" realm. :)
    >> >>>
    >> >>> Bob Barrows
    >> >>>
    >> >>> --
    >> >>> Microsoft MVP -- ASP/ASP.NET
    >> >>> Please reply to the newsgroup. The email account listed in my From
    >> >>> header is my spam trap, so I don't check it very often. You will get
    >> >>> a quicker response by posting to the newsgroup.
    >> >
    >> > --
    >> > Microsoft MVP -- ASP/ASP.NET
    >> > Please reply to the newsgroup. The email account listed in my From
    >> > header is my spam trap, so I don't check it very often. You will get a
    >> > quicker response by posting to the newsgroup.
    >> >
    >> >

    >>
    >>

    >
    >
    Jeff, Dec 21, 2005
    #11
  12. Jeff

    Bob Lehmann Guest

    >> I do want all fields that are created in the SQL that is in access. Is
    this still wrong??
    Yes.

    Bob Lehmann

    "Jeff" <> wrote in message
    news:...
    >
    > "Bob Lehmann" <> wrote in message
    > news:...
    > >>> First, it is displaying 51 records.

    > > That is because you have a tie somewhere. For example, if you had
    > > 1,2,3,3,4,5 as values, and selected top 5, you would get back 6 rows.

    >
    > I thought that might be the reason actually.
    > >
    > >>> It prompted me for a Parameter.

    > > Is "TotalWins" a column in your table?

    > Yes, TotalWins IS a field.
    > >
    > > PS - Quit using "*" for your SELECT. Explicitly name the columns you

    want
    > I am doing that because I do want all fields that are created in the SQL
    > that is in access. Is this still wrong??
    >
    > > returned.
    > >
    > > Bob Lehmann
    > >
    > > "Jeff" <> wrote in message
    > > news:...
    > >> Ok. Let me show you what I have now. Here is my script on the asp page:
    > >> <%
    > >> set admin5 = conn.execute("select top 50 * from final order by

    TotalWins
    > >> DESC")
    > >>
    > >> numRealPos = 0
    > >> numDisplayPos = 1
    > >> do while not admin5.eof
    > >> var1 = admin5.fields.item("username").value
    > >> var2 = admin5.fields.item("TotalMatches").value
    > >> var3 = admin5.fields.item("TotalWins").value
    > >> var4 = admin5.fields.item("TotalExtraWIns").value
    > >> var5 = admin5.fields.item("TotalLosses").value
    > >> var6 = admin5.fields.item("TotalExtraLosses").value
    > >> var7 = admin5.fields.item("WinPercent").value
    > >>
    > >> var8 = var3 + var4
    > >> var9 = var5 + var6
    > >>
    > >> var10 = var3 * 5 ''total win points
    > >> var11 = var5 * 2 ''total losses points
    > >> var11a = var4 + var6 '' Total Extra Matches
    > >> var12 = var10 + var11 + var11a '' total points
    > >> var13 = (var8/var2) * 100
    > >> var14 = formatnumber(var13,0)
    > >>
    > >> numRealPos = numRealPos + 1
    > >> If Not var1 = numLastValue Then
    > >> numLastValue = var3
    > >> numDisplayPos = numRealPos
    > >> End If
    > >>
    > >> %>
    > >>
    > >> <tr>
    > >> <td width="2%" bgcolor="#FFFFFF" align="center"

    style="border-style:
    > >> solid; border-width: 1px"><b><%=numDisplayPos%>&nbsp;</b></td>
    > >> <td width="2%" bgcolor="#FFFFFF" align="center"

    style="border-style:
    > >> solid; border-width: 1px"><b><%=var1%>&nbsp;</b></td>
    > >> <td width="2%" bgcolor="#FFFFFF" align="center"

    style="border-style:
    > >> solid; border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td>
    > >> <td width="2%" bgcolor="#FFFFFF" align="center"

    style="border-style:
    > >> solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
    > >> <td width="2%" bgcolor="#FFFFFF" align="center"

    style="border-style:
    > >> solid; border-width: 1px"><b><%=var12%>&nbsp;</b></td>
    > >> </tr>
    > >> <%
    > >> admin5.movenext
    > >> loop
    > >> %>
    > >> Now it starts off sorting correct, but 2 things appear to be working

    > > wrong.
    > >> First, it is displaying 51 records. Second, down around Rank 32, it
    > >> starts
    > >> going off sort.
    > >> http://www.logotour.com/tour_rankings.asp
    > >> check out what I mean. I tried to put the order clause in the SQL in

    > > Access,
    > >> but then it wouldn't work. It prompted me for a Parameter.
    > >> Any Ideas??
    > >> Jeff
    > >>
    > >>
    > >> Now the problem is, the total
    > >> "Bob Barrows [MVP]" <> wrote in message
    > >> news:...
    > >> > No. After. The ORDER BY always comes last. (The Access Query Builder
    > >> > allows
    > >> > you to answer these types of questions yourself)
    > >> >
    > >> > Jeff wrote:
    > >> >> Now if I wanted to sort by TotalWins, would I just add that in

    before
    > >> >> the GROUP BY??
    > >> >>
    > >> >>
    > >> >> "Bob Barrows [MVP]" <> wrote in message
    > >> >> news:%...
    > >> >>> Jeff wrote:
    > >> >>>> Ok gang. Here is something complicated, well, at least to me

    anyway.
    > >> >>>> Using Access DB
    > >> >>>>
    > >> >>>> I have a table in my DB called members. In that table, I have 2
    > >> >>>> tables I will be using "username" and "points"
    > >> >>>
    > >> >>> Please include datatypes in your descriptions of your tables
    > >> >>>
    > >> >>> Table: Members
    > >> >>> Columns: username Text
    > >> >>> points Number? Long? Single?
    > >> >>>
    > >> >>>>
    > >> >>>> Now, I also have a table called all_matches. This table contains
    > >> >>>> every match report. Over 25,000 of them. I have a "username" field
    > >> >>>> an "outcome" field an "username1" field and "extra_match" field.
    > >> >>>
    > >> >>> Same deal. What is the purpose of "username1"?
    > >> >>>
    > >> >>>>
    > >> >>>> What I need to do, is make a query that takes the username from
    > >> >>>> table "members" and find :
    > >> >>>> using the "username" field in the members table, i need to obtain
    > >> >>>> stats that give me the following.
    > >> >>>>
    > >> >>>> 1.) how many total matches played. Which would simply be a count

    of
    > >> >>>> the username field in the all_matches table. Since people report

    the
    > >> >>>> match win or lose.
    > >> >>>
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches
    > >> >>> FROM members as mb left join matches as m
    > >> >>> ON mb.username= m.username
    > >> >>> GROUP BY mb.username
    > >> >>>
    > >> >>>
    > >> >>>> 2.) how many wins, where extra_match = 0. This would be a count of
    > >> >>>> username in the all_matches table where "outcome" = 'Win' and
    > >> >>>> extra_match=0.
    > >> >>>
    > >> >>> <the FROM and GROUP clauses will not change, so I will not repeat
    > >> >>> then>
    > >> >>>
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    > >> >>>
    > >> >>>
    > >> >>>> 3.) how many wins, where extra_match = 1. This would be a count of
    > >> >>>> username in the all_matches table where "outcome" = 'Win' and
    > >> >>>> extra_match=1
    > >> >>>
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
    > >> >>>
    > >> >>>
    > >> >>>
    > >> >>>> 4.) how many losses, where extra_match = 0. This would be a count

    of
    > >> >>>> username in the all_matches table where "outcome" = 'Lose' and
    > >> >>>> extra_match=0..
    > >> >>>
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as

    TotalExtraWins,
    > >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
    > >> >>>
    > >> >>>
    > >> >>>> 5.) how many losses, where extra_match = 1. This would be a count

    of
    > >> >>>> username in the all_matches table where "outcome" = 'Lose' and
    > >> >>>> extra_match=1.
    > >> >>>
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as

    TotalExtraWins,
    > >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    > >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    > >> >>> TotalExtraLosses
    > >> >>>
    > >> >>>
    > >> >>>> 6.) Then divide the wins into the total games and get a win %
    > >> >>>
    > >> >>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as

    TotalExtraWins,
    > >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    > >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    > >> >>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND

    [extra_match]=0,1,0))
    > >> >>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    > >> >>> count(m.username) as WinPercent
    > >> >>> <snip>
    > >> >>>> I know this is a lot,
    > >> >>>> But any help would be appreciated. I know basic SQL, but I don't
    > >> >>>> know inner and outer joins.
    > >> >>>
    > >> >>> :)
    > >> >>> That IS "basic SQL" ...
    > >> >>> It's the GROUP BY and the use of Iif that brings this into the
    > >> >>> "advanced" realm. :)
    > >> >>>
    > >> >>> Bob Barrows
    > >> >>>
    > >> >>> --
    > >> >>> Microsoft MVP -- ASP/ASP.NET
    > >> >>> Please reply to the newsgroup. The email account listed in my From
    > >> >>> header is my spam trap, so I don't check it very often. You will

    get
    > >> >>> a quicker response by posting to the newsgroup.
    > >> >
    > >> > --
    > >> > Microsoft MVP -- ASP/ASP.NET
    > >> > Please reply to the newsgroup. The email account listed in my From
    > >> > header is my spam trap, so I don't check it very often. You will get

    a
    > >> > quicker response by posting to the newsgroup.
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
    Bob Lehmann, Dec 21, 2005
    #12
  13. Jeff

    Bob Lehmann Guest

    >> But this still Doesn't answer why it is going out of sort 2/3 of the way
    down, does it??
    You said you weren't able to use an ORDER BY clause w/o problems.

    Response.Write your sql - not the code used to generate it - and maybe
    someone can help.

    Bob Lehmann

    "Jeff" <> wrote in message
    news:...
    > But this still Doesn't answer why it is going out of sort 2/3 of the way
    > down, does it??
    >
    > Jeff
    >
    >
    > "Bob Lehmann" <> wrote in message
    > news:...
    > >>> First, it is displaying 51 records.

    > > That is because you have a tie somewhere. For example, if you had
    > > 1,2,3,3,4,5 as values, and selected top 5, you would get back 6 rows.
    > >
    > >>> It prompted me for a Parameter.

    > > Is "TotalWins" a column in your table?
    > >
    > > PS - Quit using "*" for your SELECT. Explicitly name the columns you

    want
    > > returned.
    > >
    > > Bob Lehmann
    > >
    > > "Jeff" <> wrote in message
    > > news:...
    > >> Ok. Let me show you what I have now. Here is my script on the asp page:
    > >> <%
    > >> set admin5 = conn.execute("select top 50 * from final order by

    TotalWins
    > >> DESC")
    > >>
    > >> numRealPos = 0
    > >> numDisplayPos = 1
    > >> do while not admin5.eof
    > >> var1 = admin5.fields.item("username").value
    > >> var2 = admin5.fields.item("TotalMatches").value
    > >> var3 = admin5.fields.item("TotalWins").value
    > >> var4 = admin5.fields.item("TotalExtraWIns").value
    > >> var5 = admin5.fields.item("TotalLosses").value
    > >> var6 = admin5.fields.item("TotalExtraLosses").value
    > >> var7 = admin5.fields.item("WinPercent").value
    > >>
    > >> var8 = var3 + var4
    > >> var9 = var5 + var6
    > >>
    > >> var10 = var3 * 5 ''total win points
    > >> var11 = var5 * 2 ''total losses points
    > >> var11a = var4 + var6 '' Total Extra Matches
    > >> var12 = var10 + var11 + var11a '' total points
    > >> var13 = (var8/var2) * 100
    > >> var14 = formatnumber(var13,0)
    > >>
    > >> numRealPos = numRealPos + 1
    > >> If Not var1 = numLastValue Then
    > >> numLastValue = var3
    > >> numDisplayPos = numRealPos
    > >> End If
    > >>
    > >> %>
    > >>
    > >> <tr>
    > >> <td width="2%" bgcolor="#FFFFFF" align="center"

    style="border-style:
    > >> solid; border-width: 1px"><b><%=numDisplayPos%>&nbsp;</b></td>
    > >> <td width="2%" bgcolor="#FFFFFF" align="center"

    style="border-style:
    > >> solid; border-width: 1px"><b><%=var1%>&nbsp;</b></td>
    > >> <td width="2%" bgcolor="#FFFFFF" align="center"

    style="border-style:
    > >> solid; border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td>
    > >> <td width="2%" bgcolor="#FFFFFF" align="center"

    style="border-style:
    > >> solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
    > >> <td width="2%" bgcolor="#FFFFFF" align="center"

    style="border-style:
    > >> solid; border-width: 1px"><b><%=var12%>&nbsp;</b></td>
    > >> </tr>
    > >> <%
    > >> admin5.movenext
    > >> loop
    > >> %>
    > >> Now it starts off sorting correct, but 2 things appear to be working

    > > wrong.
    > >> First, it is displaying 51 records. Second, down around Rank 32, it
    > >> starts
    > >> going off sort.
    > >> http://www.logotour.com/tour_rankings.asp
    > >> check out what I mean. I tried to put the order clause in the SQL in

    > > Access,
    > >> but then it wouldn't work. It prompted me for a Parameter.
    > >> Any Ideas??
    > >> Jeff
    > >>
    > >>
    > >> Now the problem is, the total
    > >> "Bob Barrows [MVP]" <> wrote in message
    > >> news:...
    > >> > No. After. The ORDER BY always comes last. (The Access Query Builder
    > >> > allows
    > >> > you to answer these types of questions yourself)
    > >> >
    > >> > Jeff wrote:
    > >> >> Now if I wanted to sort by TotalWins, would I just add that in

    before
    > >> >> the GROUP BY??
    > >> >>
    > >> >>
    > >> >> "Bob Barrows [MVP]" <> wrote in message
    > >> >> news:%...
    > >> >>> Jeff wrote:
    > >> >>>> Ok gang. Here is something complicated, well, at least to me

    anyway.
    > >> >>>> Using Access DB
    > >> >>>>
    > >> >>>> I have a table in my DB called members. In that table, I have 2
    > >> >>>> tables I will be using "username" and "points"
    > >> >>>
    > >> >>> Please include datatypes in your descriptions of your tables
    > >> >>>
    > >> >>> Table: Members
    > >> >>> Columns: username Text
    > >> >>> points Number? Long? Single?
    > >> >>>
    > >> >>>>
    > >> >>>> Now, I also have a table called all_matches. This table contains
    > >> >>>> every match report. Over 25,000 of them. I have a "username" field
    > >> >>>> an "outcome" field an "username1" field and "extra_match" field.
    > >> >>>
    > >> >>> Same deal. What is the purpose of "username1"?
    > >> >>>
    > >> >>>>
    > >> >>>> What I need to do, is make a query that takes the username from
    > >> >>>> table "members" and find :
    > >> >>>> using the "username" field in the members table, i need to obtain
    > >> >>>> stats that give me the following.
    > >> >>>>
    > >> >>>> 1.) how many total matches played. Which would simply be a count

    of
    > >> >>>> the username field in the all_matches table. Since people report

    the
    > >> >>>> match win or lose.
    > >> >>>
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches
    > >> >>> FROM members as mb left join matches as m
    > >> >>> ON mb.username= m.username
    > >> >>> GROUP BY mb.username
    > >> >>>
    > >> >>>
    > >> >>>> 2.) how many wins, where extra_match = 0. This would be a count of
    > >> >>>> username in the all_matches table where "outcome" = 'Win' and
    > >> >>>> extra_match=0.
    > >> >>>
    > >> >>> <the FROM and GROUP clauses will not change, so I will not repeat
    > >> >>> then>
    > >> >>>
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    > >> >>>
    > >> >>>
    > >> >>>> 3.) how many wins, where extra_match = 1. This would be a count of
    > >> >>>> username in the all_matches table where "outcome" = 'Win' and
    > >> >>>> extra_match=1
    > >> >>>
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
    > >> >>>
    > >> >>>
    > >> >>>
    > >> >>>> 4.) how many losses, where extra_match = 0. This would be a count

    of
    > >> >>>> username in the all_matches table where "outcome" = 'Lose' and
    > >> >>>> extra_match=0..
    > >> >>>
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as

    TotalExtraWins,
    > >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
    > >> >>>
    > >> >>>
    > >> >>>> 5.) how many losses, where extra_match = 1. This would be a count

    of
    > >> >>>> username in the all_matches table where "outcome" = 'Lose' and
    > >> >>>> extra_match=1.
    > >> >>>
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as

    TotalExtraWins,
    > >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    > >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    > >> >>> TotalExtraLosses
    > >> >>>
    > >> >>>
    > >> >>>> 6.) Then divide the wins into the total games and get a win %
    > >> >>>
    > >> >>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
    > >> >>> SELECT mb.username, count(m.username) as TotalMatches,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    > >> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as

    TotalExtraWins,
    > >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    > >> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    > >> >>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND

    [extra_match]=0,1,0))
    > >> >>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    > >> >>> count(m.username) as WinPercent
    > >> >>> <snip>
    > >> >>>> I know this is a lot,
    > >> >>>> But any help would be appreciated. I know basic SQL, but I don't
    > >> >>>> know inner and outer joins.
    > >> >>>
    > >> >>> :)
    > >> >>> That IS "basic SQL" ...
    > >> >>> It's the GROUP BY and the use of Iif that brings this into the
    > >> >>> "advanced" realm. :)
    > >> >>>
    > >> >>> Bob Barrows
    > >> >>>
    > >> >>> --
    > >> >>> Microsoft MVP -- ASP/ASP.NET
    > >> >>> Please reply to the newsgroup. The email account listed in my From
    > >> >>> header is my spam trap, so I don't check it very often. You will

    get
    > >> >>> a quicker response by posting to the newsgroup.
    > >> >
    > >> > --
    > >> > Microsoft MVP -- ASP/ASP.NET
    > >> > Please reply to the newsgroup. The email account listed in my From
    > >> > header is my spam trap, so I don't check it very often. You will get

    a
    > >> > quicker response by posting to the newsgroup.
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
    Bob Lehmann, Dec 21, 2005
    #13
  14. Jeff

    Jeff Guest

    Waiting for Bob Barrows {MVP} to reply, since he is the one who helped me to
    begin with.




    "Jeff" <> wrote in message
    news:...
    > But this still Doesn't answer why it is going out of sort 2/3 of the way
    > down, does it??
    >
    > Jeff
    >
    >
    > "Bob Lehmann" <> wrote in message
    > news:...
    >>>> First, it is displaying 51 records.

    >> That is because you have a tie somewhere. For example, if you had
    >> 1,2,3,3,4,5 as values, and selected top 5, you would get back 6 rows.
    >>
    >>>> It prompted me for a Parameter.

    >> Is "TotalWins" a column in your table?
    >>
    >> PS - Quit using "*" for your SELECT. Explicitly name the columns you want
    >> returned.
    >>
    >> Bob Lehmann
    >>
    >> "Jeff" <> wrote in message
    >> news:...
    >>> Ok. Let me show you what I have now. Here is my script on the asp page:
    >>> <%
    >>> set admin5 = conn.execute("select top 50 * from final order by TotalWins
    >>> DESC")
    >>>
    >>> numRealPos = 0
    >>> numDisplayPos = 1
    >>> do while not admin5.eof
    >>> var1 = admin5.fields.item("username").value
    >>> var2 = admin5.fields.item("TotalMatches").value
    >>> var3 = admin5.fields.item("TotalWins").value
    >>> var4 = admin5.fields.item("TotalExtraWIns").value
    >>> var5 = admin5.fields.item("TotalLosses").value
    >>> var6 = admin5.fields.item("TotalExtraLosses").value
    >>> var7 = admin5.fields.item("WinPercent").value
    >>>
    >>> var8 = var3 + var4
    >>> var9 = var5 + var6
    >>>
    >>> var10 = var3 * 5 ''total win points
    >>> var11 = var5 * 2 ''total losses points
    >>> var11a = var4 + var6 '' Total Extra Matches
    >>> var12 = var10 + var11 + var11a '' total points
    >>> var13 = (var8/var2) * 100
    >>> var14 = formatnumber(var13,0)
    >>>
    >>> numRealPos = numRealPos + 1
    >>> If Not var1 = numLastValue Then
    >>> numLastValue = var3
    >>> numDisplayPos = numRealPos
    >>> End If
    >>>
    >>> %>
    >>>
    >>> <tr>
    >>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >>> solid; border-width: 1px"><b><%=numDisplayPos%>&nbsp;</b></td>
    >>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >>> solid; border-width: 1px"><b><%=var1%>&nbsp;</b></td>
    >>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >>> solid; border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td>
    >>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >>> solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
    >>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >>> solid; border-width: 1px"><b><%=var12%>&nbsp;</b></td>
    >>> </tr>
    >>> <%
    >>> admin5.movenext
    >>> loop
    >>> %>
    >>> Now it starts off sorting correct, but 2 things appear to be working

    >> wrong.
    >>> First, it is displaying 51 records. Second, down around Rank 32, it
    >>> starts
    >>> going off sort.
    >>> http://www.logotour.com/tour_rankings.asp
    >>> check out what I mean. I tried to put the order clause in the SQL in

    >> Access,
    >>> but then it wouldn't work. It prompted me for a Parameter.
    >>> Any Ideas??
    >>> Jeff
    >>>
    >>>
    >>> Now the problem is, the total
    >>> "Bob Barrows [MVP]" <> wrote in message
    >>> news:...
    >>> > No. After. The ORDER BY always comes last. (The Access Query Builder
    >>> > allows
    >>> > you to answer these types of questions yourself)
    >>> >
    >>> > Jeff wrote:
    >>> >> Now if I wanted to sort by TotalWins, would I just add that in before
    >>> >> the GROUP BY??
    >>> >>
    >>> >>
    >>> >> "Bob Barrows [MVP]" <> wrote in message
    >>> >> news:%...
    >>> >>> Jeff wrote:
    >>> >>>> Ok gang. Here is something complicated, well, at least to me
    >>> >>>> anyway.
    >>> >>>> Using Access DB
    >>> >>>>
    >>> >>>> I have a table in my DB called members. In that table, I have 2
    >>> >>>> tables I will be using "username" and "points"
    >>> >>>
    >>> >>> Please include datatypes in your descriptions of your tables
    >>> >>>
    >>> >>> Table: Members
    >>> >>> Columns: username Text
    >>> >>> points Number? Long? Single?
    >>> >>>
    >>> >>>>
    >>> >>>> Now, I also have a table called all_matches. This table contains
    >>> >>>> every match report. Over 25,000 of them. I have a "username" field
    >>> >>>> an "outcome" field an "username1" field and "extra_match" field.
    >>> >>>
    >>> >>> Same deal. What is the purpose of "username1"?
    >>> >>>
    >>> >>>>
    >>> >>>> What I need to do, is make a query that takes the username from
    >>> >>>> table "members" and find :
    >>> >>>> using the "username" field in the members table, i need to obtain
    >>> >>>> stats that give me the following.
    >>> >>>>
    >>> >>>> 1.) how many total matches played. Which would simply be a count of
    >>> >>>> the username field in the all_matches table. Since people report
    >>> >>>> the
    >>> >>>> match win or lose.
    >>> >>>
    >>> >>> SELECT mb.username, count(m.username) as TotalMatches
    >>> >>> FROM members as mb left join matches as m
    >>> >>> ON mb.username= m.username
    >>> >>> GROUP BY mb.username
    >>> >>>
    >>> >>>
    >>> >>>> 2.) how many wins, where extra_match = 0. This would be a count of
    >>> >>>> username in the all_matches table where "outcome" = 'Win' and
    >>> >>>> extra_match=0.
    >>> >>>
    >>> >>> <the FROM and GROUP clauses will not change, so I will not repeat
    >>> >>> then>
    >>> >>>
    >>> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    >>> >>>
    >>> >>>
    >>> >>>> 3.) how many wins, where extra_match = 1. This would be a count of
    >>> >>>> username in the all_matches table where "outcome" = 'Win' and
    >>> >>>> extra_match=1
    >>> >>>
    >>> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins
    >>> >>>
    >>> >>>
    >>> >>>
    >>> >>>> 4.) how many losses, where extra_match = 0. This would be a count
    >>> >>>> of
    >>> >>>> username in the all_matches table where "outcome" = 'Lose' and
    >>> >>>> extra_match=0..
    >>> >>>
    >>> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >>> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses
    >>> >>>
    >>> >>>
    >>> >>>> 5.) how many losses, where extra_match = 1. This would be a count
    >>> >>>> of
    >>> >>>> username in the all_matches table where "outcome" = 'Lose' and
    >>> >>>> extra_match=1.
    >>> >>>
    >>> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >>> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >>> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >>> >>> TotalExtraLosses
    >>> >>>
    >>> >>>
    >>> >>>> 6.) Then divide the wins into the total games and get a win %
    >>> >>>
    >>> >>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
    >>> >>> SELECT mb.username, count(m.username) as TotalMatches,
    >>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>> >>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as TotalExtraWins,
    >>> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=0,1,0)) as TotalLosses,
    >>> >>> SUM(Iif([outcome]='Loss' AND [extra_match]=1,1,0)) as
    >>> >>> TotalExtraLosses, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
    >>> >>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    >>> >>> count(m.username) as WinPercent
    >>> >>> <snip>
    >>> >>>> I know this is a lot,
    >>> >>>> But any help would be appreciated. I know basic SQL, but I don't
    >>> >>>> know inner and outer joins.
    >>> >>>
    >>> >>> :)
    >>> >>> That IS "basic SQL" ...
    >>> >>> It's the GROUP BY and the use of Iif that brings this into the
    >>> >>> "advanced" realm. :)
    >>> >>>
    >>> >>> Bob Barrows
    >>> >>>
    >>> >>> --
    >>> >>> Microsoft MVP -- ASP/ASP.NET
    >>> >>> Please reply to the newsgroup. The email account listed in my From
    >>> >>> header is my spam trap, so I don't check it very often. You will get
    >>> >>> a quicker response by posting to the newsgroup.
    >>> >
    >>> > --
    >>> > Microsoft MVP -- ASP/ASP.NET
    >>> > Please reply to the newsgroup. The email account listed in my From
    >>> > header is my spam trap, so I don't check it very often. You will get a
    >>> > quicker response by posting to the newsgroup.
    >>> >
    >>> >
    >>>
    >>>

    >>
    >>

    >
    >
    Jeff, Dec 21, 2005
    #14
  15. When you run this query in Access, do you get similar results?
    Jeff wrote:
    > Waiting for Bob Barrows {MVP} to reply, since he is the one who
    > helped me to begin with.
    >
    >
    >
    >
    > "Jeff" <> wrote in message
    > news:...
    >> But this still Doesn't answer why it is going out of sort 2/3 of the
    >> way down, does it??
    >>
    >> Jeff
    >>
    >>
    >> "Bob Lehmann" <> wrote in message
    >> news:...
    >>>>> First, it is displaying 51 records.
    >>> That is because you have a tie somewhere. For example, if you had
    >>> 1,2,3,3,4,5 as values, and selected top 5, you would get back 6
    >>> rows.
    >>>
    >>>>> It prompted me for a Parameter.
    >>> Is "TotalWins" a column in your table?
    >>>
    >>> PS - Quit using "*" for your SELECT. Explicitly name the columns
    >>> you want returned.
    >>>
    >>> Bob Lehmann
    >>>
    >>> "Jeff" <> wrote in message
    >>> news:...
    >>>> Ok. Let me show you what I have now. Here is my script on the asp
    >>>> page: <%
    >>>> set admin5 = conn.execute("select top 50 * from final order by
    >>>> TotalWins DESC")
    >>>>
    >>>> numRealPos = 0
    >>>> numDisplayPos = 1
    >>>> do while not admin5.eof
    >>>> var1 = admin5.fields.item("username").value
    >>>> var2 = admin5.fields.item("TotalMatches").value
    >>>> var3 = admin5.fields.item("TotalWins").value
    >>>> var4 = admin5.fields.item("TotalExtraWIns").value
    >>>> var5 = admin5.fields.item("TotalLosses").value
    >>>> var6 = admin5.fields.item("TotalExtraLosses").value
    >>>> var7 = admin5.fields.item("WinPercent").value
    >>>>
    >>>> var8 = var3 + var4
    >>>> var9 = var5 + var6
    >>>>
    >>>> var10 = var3 * 5 ''total win points
    >>>> var11 = var5 * 2 ''total losses points
    >>>> var11a = var4 + var6 '' Total Extra Matches
    >>>> var12 = var10 + var11 + var11a '' total points
    >>>> var13 = (var8/var2) * 100
    >>>> var14 = formatnumber(var13,0)
    >>>>
    >>>> numRealPos = numRealPos + 1
    >>>> If Not var1 = numLastValue Then
    >>>> numLastValue = var3
    >>>> numDisplayPos = numRealPos
    >>>> End If
    >>>>
    >>>> %>
    >>>>
    >>>> <tr>
    >>>> <td width="2%" bgcolor="#FFFFFF" align="center"
    >>>> style="border-style: solid; border-width:
    >>>> 1px"><b><%=numDisplayPos%>&nbsp;</b></td> <td width="2%"
    >>>> bgcolor="#FFFFFF" align="center" style="border-style: solid;
    >>>> border-width: 1px"><b><%=var1%>&nbsp;</b></td> <td width="2%"
    >>>> bgcolor="#FFFFFF" align="center" style="border-style: solid;
    >>>> border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td> <td
    >>>> width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >>>> solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
    >>>> <td width="2%" bgcolor="#FFFFFF" align="center"
    >>>> style="border-style: solid; border-width:
    >>>> 1px"><b><%=var12%>&nbsp;</b></td> </tr> <%
    >>>> admin5.movenext
    >>>> loop
    >>>> %>
    >>>> Now it starts off sorting correct, but 2 things appear to be
    >>>> working wrong. First, it is displaying 51 records. Second, down
    >>>> around Rank 32, it starts
    >>>> going off sort.
    >>>> http://www.logotour.com/tour_rankings.asp
    >>>> check out what I mean. I tried to put the order clause in the SQL
    >>>> in Access, but then it wouldn't work. It prompted me for a
    >>>> Parameter.
    >>>> Any Ideas??
    >>>> Jeff
    >>>>
    >>>>
    >>>> Now the problem is, the total
    >>>> "Bob Barrows [MVP]" <> wrote in message
    >>>> news:...
    >>>>> No. After. The ORDER BY always comes last. (The Access Query
    >>>>> Builder allows
    >>>>> you to answer these types of questions yourself)
    >>>>>
    >>>>> Jeff wrote:
    >>>>>> Now if I wanted to sort by TotalWins, would I just add that in
    >>>>>> before the GROUP BY??
    >>>>>>
    >>>>>>
    >>>>>> "Bob Barrows [MVP]" <> wrote in message
    >>>>>> news:%...
    >>>>>>> Jeff wrote:
    >>>>>>>> Ok gang. Here is something complicated, well, at least to me
    >>>>>>>> anyway.
    >>>>>>>> Using Access DB
    >>>>>>>>
    >>>>>>>> I have a table in my DB called members. In that table, I have 2
    >>>>>>>> tables I will be using "username" and "points"
    >>>>>>>
    >>>>>>> Please include datatypes in your descriptions of your tables
    >>>>>>>
    >>>>>>> Table: Members
    >>>>>>> Columns: username Text
    >>>>>>> points Number? Long? Single?
    >>>>>>>
    >>>>>>>>
    >>>>>>>> Now, I also have a table called all_matches. This table
    >>>>>>>> contains every match report. Over 25,000 of them. I have a
    >>>>>>>> "username" field an "outcome" field an "username1" field and
    >>>>>>>> "extra_match" field.
    >>>>>>>
    >>>>>>> Same deal. What is the purpose of "username1"?
    >>>>>>>
    >>>>>>>>
    >>>>>>>> What I need to do, is make a query that takes the username from
    >>>>>>>> table "members" and find :
    >>>>>>>> using the "username" field in the members table, i need to
    >>>>>>>> obtain stats that give me the following.
    >>>>>>>>
    >>>>>>>> 1.) how many total matches played. Which would simply be a
    >>>>>>>> count of the username field in the all_matches table. Since
    >>>>>>>> people report the
    >>>>>>>> match win or lose.
    >>>>>>>
    >>>>>>> SELECT mb.username, count(m.username) as TotalMatches
    >>>>>>> FROM members as mb left join matches as m
    >>>>>>> ON mb.username= m.username
    >>>>>>> GROUP BY mb.username
    >>>>>>>
    >>>>>>>
    >>>>>>>> 2.) how many wins, where extra_match = 0. This would be a
    >>>>>>>> count of username in the all_matches table where "outcome" =
    >>>>>>>> 'Win' and extra_match=0.
    >>>>>>>
    >>>>>>> <the FROM and GROUP clauses will not change, so I will not
    >>>>>>> repeat then>
    >>>>>>>
    >>>>>>> SELECT mb.username, count(m.username) as TotalMatches,
    >>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    >>>>>>>
    >>>>>>>
    >>>>>>>> 3.) how many wins, where extra_match = 1. This would be a
    >>>>>>>> count of username in the all_matches table where "outcome" =
    >>>>>>>> 'Win' and extra_match=1
    >>>>>>>
    >>>>>>> SELECT mb.username, count(m.username) as TotalMatches,
    >>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as
    >>>>>>> TotalExtraWins
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>> 4.) how many losses, where extra_match = 0. This would be a
    >>>>>>>> count of
    >>>>>>>> username in the all_matches table where "outcome" = 'Lose' and
    >>>>>>>> extra_match=0..
    >>>>>>>
    >>>>>>> SELECT mb.username, count(m.username) as TotalMatches,
    >>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as
    >>>>>>> TotalExtraWins, SUM(Iif([outcome]='Loss' AND
    >>>>>>> [extra_match]=0,1,0)) as TotalLosses
    >>>>>>>
    >>>>>>>
    >>>>>>>> 5.) how many losses, where extra_match = 1. This would be a
    >>>>>>>> count of
    >>>>>>>> username in the all_matches table where "outcome" = 'Lose' and
    >>>>>>>> extra_match=1.
    >>>>>>>
    >>>>>>> SELECT mb.username, count(m.username) as TotalMatches,
    >>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as
    >>>>>>> TotalExtraWins, SUM(Iif([outcome]='Loss' AND
    >>>>>>> [extra_match]=0,1,0)) as TotalLosses, SUM(Iif([outcome]='Loss'
    >>>>>>> AND [extra_match]=1,1,0)) as TotalExtraLosses
    >>>>>>>
    >>>>>>>
    >>>>>>>> 6.) Then divide the wins into the total games and get a win %
    >>>>>>>
    >>>>>>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
    >>>>>>> SELECT mb.username, count(m.username) as TotalMatches,
    >>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as
    >>>>>>> TotalExtraWins, SUM(Iif([outcome]='Loss' AND
    >>>>>>> [extra_match]=0,1,0)) as TotalLosses, SUM(Iif([outcome]='Loss'
    >>>>>>> AND [extra_match]=1,1,0)) as TotalExtraLosses,
    >>>>>>> (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
    >>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    >>>>>>> count(m.username) as WinPercent <snip>
    >>>>>>>> I know this is a lot,
    >>>>>>>> But any help would be appreciated. I know basic SQL, but I
    >>>>>>>> don't know inner and outer joins.
    >>>>>>>
    >>>>>>> :)
    >>>>>>> That IS "basic SQL" ...
    >>>>>>> It's the GROUP BY and the use of Iif that brings this into the
    >>>>>>> "advanced" realm. :)
    >>>>>>>
    >>>>>>> Bob Barrows
    >>>>>>>
    >>>>>>> --
    >>>>>>> Microsoft MVP -- ASP/ASP.NET
    >>>>>>> Please reply to the newsgroup. The email account listed in my
    >>>>>>> From header is my spam trap, so I don't check it very often.
    >>>>>>> You will get a quicker response by posting to the newsgroup.
    >>>>>
    >>>>> --
    >>>>> Microsoft MVP -- ASP/ASP.NET
    >>>>> Please reply to the newsgroup. The email account listed in my From
    >>>>> header is my spam trap, so I don't check it very often. You will
    >>>>> get a quicker response by posting to the newsgroup.


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Dec 21, 2005
    #15
  16. Jeff wrote:
    > Ok. Let me show you what I have now. Here is my script on the asp
    > page: <%
    > set admin5 = conn.execute("select top 50 * from final order by


    I prefer to list the fields instead of using * here.

    > TotalWins DESC")
    >

    This is very inefficient code. You will probably see noticable performance
    improvements by using a GetRows array, like this:

    dim arData, i
    if not admin5.eof then arData=admin5.getrows

    'This is a huge benefit - you can disconnect from the
    'database while you process your data:
    admin5.close:set admin5=nothing
    conn.close: set conn = nothing

    if isarray(ardata) then
    for i=0 to ubound9arData,2)
    var1 = arData(0,i)
    var2 = arData(1,i)
    var3 = arData(2,i)
    var4 = arData(3,i)
    var5 = arData(4,i)
    var6 = arData(5,i)
    var7 = arData(6,i)
    'do the rest of your calcs
    next
    else
    response.write "No data was returned"
    end if

    HTH,
    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Dec 21, 2005
    #16
  17. Jeff

    Jeff Guest

    When (in Access) I put in any kind of ORDER BY, it prompts me for a
    paramater. So what I was doing was calling the query from asp page. In the
    query itself, when I look at in access, it is sorted by username.
    So in my query on the asp page, I am calling this:
    <%
    set admin5 = conn.execute("SELECT top 50 username, TotalMatches, TotalWins,
    TotalExtraWins, TotalLoses, TotalExtraLosses, WinPercent FROM final ORDER BY
    TotalWins DESC")

    Now when this displays, it keeps the correct order, until about 2/3 way down
    the list. THen the sort gets out of order.
    http://www.logotour.com/tour_rankings.asp
    I think the secret is getting the order to work in the query that is nested
    in access. If I get that to work, then I think the output will work as well.
    I tried adding ORDER BY TotalWins in the query, but it prompts me for a
    parameter.





    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > When you run this query in Access, do you get similar results?
    > Jeff wrote:
    >> Waiting for Bob Barrows {MVP} to reply, since he is the one who
    >> helped me to begin with.
    >>
    >>
    >>
    >>
    >> "Jeff" <> wrote in message
    >> news:...
    >>> But this still Doesn't answer why it is going out of sort 2/3 of the
    >>> way down, does it??
    >>>
    >>> Jeff
    >>>
    >>>
    >>> "Bob Lehmann" <> wrote in message
    >>> news:...
    >>>>>> First, it is displaying 51 records.
    >>>> That is because you have a tie somewhere. For example, if you had
    >>>> 1,2,3,3,4,5 as values, and selected top 5, you would get back 6
    >>>> rows.
    >>>>
    >>>>>> It prompted me for a Parameter.
    >>>> Is "TotalWins" a column in your table?
    >>>>
    >>>> PS - Quit using "*" for your SELECT. Explicitly name the columns
    >>>> you want returned.
    >>>>
    >>>> Bob Lehmann
    >>>>
    >>>> "Jeff" <> wrote in message
    >>>> news:...
    >>>>> Ok. Let me show you what I have now. Here is my script on the asp
    >>>>> page: <%
    >>>>> set admin5 = conn.execute("select top 50 * from final order by
    >>>>> TotalWins DESC")
    >>>>>
    >>>>> numRealPos = 0
    >>>>> numDisplayPos = 1
    >>>>> do while not admin5.eof
    >>>>> var1 = admin5.fields.item("username").value
    >>>>> var2 = admin5.fields.item("TotalMatches").value
    >>>>> var3 = admin5.fields.item("TotalWins").value
    >>>>> var4 = admin5.fields.item("TotalExtraWIns").value
    >>>>> var5 = admin5.fields.item("TotalLosses").value
    >>>>> var6 = admin5.fields.item("TotalExtraLosses").value
    >>>>> var7 = admin5.fields.item("WinPercent").value
    >>>>>
    >>>>> var8 = var3 + var4
    >>>>> var9 = var5 + var6
    >>>>>
    >>>>> var10 = var3 * 5 ''total win points
    >>>>> var11 = var5 * 2 ''total losses points
    >>>>> var11a = var4 + var6 '' Total Extra Matches
    >>>>> var12 = var10 + var11 + var11a '' total points
    >>>>> var13 = (var8/var2) * 100
    >>>>> var14 = formatnumber(var13,0)
    >>>>>
    >>>>> numRealPos = numRealPos + 1
    >>>>> If Not var1 = numLastValue Then
    >>>>> numLastValue = var3
    >>>>> numDisplayPos = numRealPos
    >>>>> End If
    >>>>>
    >>>>> %>
    >>>>>
    >>>>> <tr>
    >>>>> <td width="2%" bgcolor="#FFFFFF" align="center"
    >>>>> style="border-style: solid; border-width:
    >>>>> 1px"><b><%=numDisplayPos%>&nbsp;</b></td> <td width="2%"
    >>>>> bgcolor="#FFFFFF" align="center" style="border-style: solid;
    >>>>> border-width: 1px"><b><%=var1%>&nbsp;</b></td> <td width="2%"
    >>>>> bgcolor="#FFFFFF" align="center" style="border-style: solid;
    >>>>> border-width: 1px"><b><%=var8%> - <%=var9%>&nbsp;</b></td> <td
    >>>>> width="2%" bgcolor="#FFFFFF" align="center" style="border-style:
    >>>>> solid; border-width: 1px"><b><%=var14%>&nbsp;%</b>&nbsp;</td>
    >>>>> <td width="2%" bgcolor="#FFFFFF" align="center"
    >>>>> style="border-style: solid; border-width:
    >>>>> 1px"><b><%=var12%>&nbsp;</b></td> </tr> <%
    >>>>> admin5.movenext
    >>>>> loop
    >>>>> %>
    >>>>> Now it starts off sorting correct, but 2 things appear to be
    >>>>> working wrong. First, it is displaying 51 records. Second, down
    >>>>> around Rank 32, it starts
    >>>>> going off sort.
    >>>>> http://www.logotour.com/tour_rankings.asp
    >>>>> check out what I mean. I tried to put the order clause in the SQL
    >>>>> in Access, but then it wouldn't work. It prompted me for a
    >>>>> Parameter.
    >>>>> Any Ideas??
    >>>>> Jeff
    >>>>>
    >>>>>
    >>>>> Now the problem is, the total
    >>>>> "Bob Barrows [MVP]" <> wrote in message
    >>>>> news:...
    >>>>>> No. After. The ORDER BY always comes last. (The Access Query
    >>>>>> Builder allows
    >>>>>> you to answer these types of questions yourself)
    >>>>>>
    >>>>>> Jeff wrote:
    >>>>>>> Now if I wanted to sort by TotalWins, would I just add that in
    >>>>>>> before the GROUP BY??
    >>>>>>>
    >>>>>>>
    >>>>>>> "Bob Barrows [MVP]" <> wrote in message
    >>>>>>> news:%...
    >>>>>>>> Jeff wrote:
    >>>>>>>>> Ok gang. Here is something complicated, well, at least to me
    >>>>>>>>> anyway.
    >>>>>>>>> Using Access DB
    >>>>>>>>>
    >>>>>>>>> I have a table in my DB called members. In that table, I have 2
    >>>>>>>>> tables I will be using "username" and "points"
    >>>>>>>>
    >>>>>>>> Please include datatypes in your descriptions of your tables
    >>>>>>>>
    >>>>>>>> Table: Members
    >>>>>>>> Columns: username Text
    >>>>>>>> points Number? Long? Single?
    >>>>>>>>
    >>>>>>>>>
    >>>>>>>>> Now, I also have a table called all_matches. This table
    >>>>>>>>> contains every match report. Over 25,000 of them. I have a
    >>>>>>>>> "username" field an "outcome" field an "username1" field and
    >>>>>>>>> "extra_match" field.
    >>>>>>>>
    >>>>>>>> Same deal. What is the purpose of "username1"?
    >>>>>>>>
    >>>>>>>>>
    >>>>>>>>> What I need to do, is make a query that takes the username from
    >>>>>>>>> table "members" and find :
    >>>>>>>>> using the "username" field in the members table, i need to
    >>>>>>>>> obtain stats that give me the following.
    >>>>>>>>>
    >>>>>>>>> 1.) how many total matches played. Which would simply be a
    >>>>>>>>> count of the username field in the all_matches table. Since
    >>>>>>>>> people report the
    >>>>>>>>> match win or lose.
    >>>>>>>>
    >>>>>>>> SELECT mb.username, count(m.username) as TotalMatches
    >>>>>>>> FROM members as mb left join matches as m
    >>>>>>>> ON mb.username= m.username
    >>>>>>>> GROUP BY mb.username
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>> 2.) how many wins, where extra_match = 0. This would be a
    >>>>>>>>> count of username in the all_matches table where "outcome" =
    >>>>>>>>> 'Win' and extra_match=0.
    >>>>>>>>
    >>>>>>>> <the FROM and GROUP clauses will not change, so I will not
    >>>>>>>> repeat then>
    >>>>>>>>
    >>>>>>>> SELECT mb.username, count(m.username) as TotalMatches,
    >>>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>> 3.) how many wins, where extra_match = 1. This would be a
    >>>>>>>>> count of username in the all_matches table where "outcome" =
    >>>>>>>>> 'Win' and extra_match=1
    >>>>>>>>
    >>>>>>>> SELECT mb.username, count(m.username) as TotalMatches,
    >>>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as
    >>>>>>>> TotalExtraWins
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>> 4.) how many losses, where extra_match = 0. This would be a
    >>>>>>>>> count of
    >>>>>>>>> username in the all_matches table where "outcome" = 'Lose' and
    >>>>>>>>> extra_match=0..
    >>>>>>>>
    >>>>>>>> SELECT mb.username, count(m.username) as TotalMatches,
    >>>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as
    >>>>>>>> TotalExtraWins, SUM(Iif([outcome]='Loss' AND
    >>>>>>>> [extra_match]=0,1,0)) as TotalLosses
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>> 5.) how many losses, where extra_match = 1. This would be a
    >>>>>>>>> count of
    >>>>>>>>> username in the all_matches table where "outcome" = 'Lose' and
    >>>>>>>>> extra_match=1.
    >>>>>>>>
    >>>>>>>> SELECT mb.username, count(m.username) as TotalMatches,
    >>>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as
    >>>>>>>> TotalExtraWins, SUM(Iif([outcome]='Loss' AND
    >>>>>>>> [extra_match]=0,1,0)) as TotalLosses, SUM(Iif([outcome]='Loss'
    >>>>>>>> AND [extra_match]=1,1,0)) as TotalExtraLosses
    >>>>>>>>
    >>>>>>>>
    >>>>>>>>> 6.) Then divide the wins into the total games and get a win %
    >>>>>>>>
    >>>>>>>> Is "wins" defined as TotalWins + TotalExtraWins? if so:
    >>>>>>>> SELECT mb.username, count(m.username) as TotalMatches,
    >>>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) as TotalWins,
    >>>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) as
    >>>>>>>> TotalExtraWins, SUM(Iif([outcome]='Loss' AND
    >>>>>>>> [extra_match]=0,1,0)) as TotalLosses, SUM(Iif([outcome]='Loss'
    >>>>>>>> AND [extra_match]=1,1,0)) as TotalExtraLosses,
    >>>>>>>> (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
    >>>>>>>> SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
    >>>>>>>> count(m.username) as WinPercent <snip>
    >>>>>>>>> I know this is a lot,
    >>>>>>>>> But any help would be appreciated. I know basic SQL, but I
    >>>>>>>>> don't know inner and outer joins.
    >>>>>>>>
    >>>>>>>> :)
    >>>>>>>> That IS "basic SQL" ...
    >>>>>>>> It's the GROUP BY and the use of Iif that brings this into the
    >>>>>>>> "advanced" realm. :)
    >>>>>>>>
    >>>>>>>> Bob Barrows
    >>>>>>>>
    >>>>>>>> --
    >>>>>>>> Microsoft MVP -- ASP/ASP.NET
    >>>>>>>> Please reply to the newsgroup. The email account listed in my
    >>>>>>>> From header is my spam trap, so I don't check it very often.
    >>>>>>>> You will get a quicker response by posting to the newsgroup.
    >>>>>>
    >>>>>> --
    >>>>>> Microsoft MVP -- ASP/ASP.NET
    >>>>>> Please reply to the newsgroup. The email account listed in my From
    >>>>>> header is my spam trap, so I don't check it very often. You will
    >>>>>> get a quicker response by posting to the newsgroup.

    >
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Jeff, Dec 21, 2005
    #17
  18. Jeff

    Jeff Guest

    if i do it this way, i could then make pages that would show the next
    50..and so on??



    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Jeff wrote:
    >> Ok. Let me show you what I have now. Here is my script on the asp
    >> page: <%
    >> set admin5 = conn.execute("select top 50 * from final order by

    >
    > I prefer to list the fields instead of using * here.
    >
    >> TotalWins DESC")
    >>

    > This is very inefficient code. You will probably see noticable performance
    > improvements by using a GetRows array, like this:
    >
    > dim arData, i
    > if not admin5.eof then arData=admin5.getrows
    >
    > 'This is a huge benefit - you can disconnect from the
    > 'database while you process your data:
    > admin5.close:set admin5=nothing
    > conn.close: set conn = nothing
    >
    > if isarray(ardata) then
    > for i=0 to ubound9arData,2)
    > var1 = arData(0,i)
    > var2 = arData(1,i)
    > var3 = arData(2,i)
    > var4 = arData(3,i)
    > var5 = arData(4,i)
    > var6 = arData(5,i)
    > var7 = arData(6,i)
    > 'do the rest of your calcs
    > next
    > else
    > response.write "No data was returned"
    > end if
    >
    > HTH,
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Jeff, Dec 21, 2005
    #18
  19. Jeff

    Jeff Guest

    And I get an error there Bob

    Microsoft VBScript compilation error '800a03ee'

    Expected ')'

    /tour_rankings.asp, line 46

    for i=0 to ubound9arData,2)
    ------------------------^"Bob Barrows [MVP]" <>
    wrote in message news:...
    > Jeff wrote:
    >> Ok. Let me show you what I have now. Here is my script on the asp
    >> page: <%
    >> set admin5 = conn.execute("select top 50 * from final order by

    >
    > I prefer to list the fields instead of using * here.
    >
    >> TotalWins DESC")
    >>

    > This is very inefficient code. You will probably see noticable performance
    > improvements by using a GetRows array, like this:
    >
    > dim arData, i
    > if not admin5.eof then arData=admin5.getrows
    >
    > 'This is a huge benefit - you can disconnect from the
    > 'database while you process your data:
    > admin5.close:set admin5=nothing
    > conn.close: set conn = nothing
    >
    > if isarray(ardata) then
    > for i=0 to ubound9arData,2)
    > var1 = arData(0,i)
    > var2 = arData(1,i)
    > var3 = arData(2,i)
    > var4 = arData(3,i)
    > var5 = arData(4,i)
    > var6 = arData(5,i)
    > var7 = arData(6,i)
    > 'do the rest of your calcs
    > next
    > else
    > response.write "No data was returned"
    > end if
    >
    > HTH,
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
    Jeff, Dec 21, 2005
    #19
  20. Jeff wrote:
    > When (in Access) I put in any kind of ORDER BY, it prompts me for a
    > paramater.


    More detail please. What is the SQL Statement that causes the parameter
    prompt?

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Dec 21, 2005
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Gareth Stretch
    Replies:
    0
    Views:
    4,747
    Gareth Stretch
    Jun 26, 2003
  2. Gene Ariani
    Replies:
    0
    Views:
    2,801
    Gene Ariani
    Aug 21, 2003
  3. Carlo v. Dango
    Replies:
    14
    Views:
    1,025
    Alex Martelli
    Oct 19, 2003
  4. Tim Chase
    Replies:
    6
    Views:
    278
    Michael J. Fromberger
    Feb 28, 2006
  5. Pyenos
    Replies:
    2
    Views:
    385
    Pyenos
    Dec 27, 2006
Loading...

Share This Page