Complicated inner join??

[Jeff]

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

 

[Bob Barrows [MVP]]

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

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

 

[Jeff]

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.

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

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]

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

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

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]]

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

 

[Bob Barrows [MVP]]

No. After. The ORDER BY always comes last. (The Access Query Builder allows
you to answer these types of questions yourself)

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

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

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]

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

No. After. The ORDER BY always comes last. (The Access Query Builder
allows
you to answer these types of questions yourself)

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

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]

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

No. After. The ORDER BY always comes last. (The Access Query Builder
allows
you to answer these types of questions yourself)

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

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]

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.
Is "TotalWins" a column in your table?

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

Bob Lehmann

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

No. After. The ORDER BY always comes last. (The Access Query Builder
allows
you to answer these types of questions yourself)

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


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]

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.

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

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

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??


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]

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

Jeff

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.
Is "TotalWins" a column in your table?

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

Bob Lehmann

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

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??


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]

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

Bob Lehmann

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.

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

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
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??


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]

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

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

Jeff

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

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
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??


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]

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

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

Jeff

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

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
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??


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]]

When you run this query in Access, do you get similar results?

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

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

Jeff

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

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
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??


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]]

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

 

[Jeff]

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.

When you run this query in Access, do you get similar results?

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

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

Jeff


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

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
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??


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]

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

 

[Jeff]

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]]

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?