By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,406 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

Complicated inner join??

P: n/a
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
Dec 20 '05 #1
Share this Question
Share on Google+
26 Replies


P: n/a
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.
Dec 20 '05 #2

P: n/a

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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
Dec 20 '05 #3

P: n/a
Now if I wanted to sort by TotalWins, would I just add that in before the
GROUP BY??
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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.

Dec 20 '05 #4

P: n/a
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.
Dec 20 '05 #5

P: n/a
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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.
Dec 20 '05 #6

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

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:eg**************@TK2MSFTNGP11.phx.gbl...
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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.

Dec 20 '05 #7

P: n/a
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eg**************@TK2MSFTNGP11.phx.gbl...
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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.

Dec 20 '05 #8

P: n/a
>> 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" <gi*****@adelphia.net> wrote in message
news:l-********************@adelphia.com... 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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eg**************@TK2MSFTNGP11.phx.gbl...
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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.


Dec 21 '05 #9

P: n/a

"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:OA**************@TK2MSFTNGP15.phx.gbl...
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" <gi*****@adelphia.net> wrote in message
news:l-********************@adelphia.com...
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eg**************@TK2MSFTNGP11.phx.gbl...
> 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]" <re******@NOyahoo.SPAMcom> wrote in message
>> news:%2****************@TK2MSFTNGP10.phx.gbl...
>>> 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.
>
>



Dec 21 '05 #10

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

Jeff
"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:OA**************@TK2MSFTNGP15.phx.gbl...
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" <gi*****@adelphia.net> wrote in message
news:l-********************@adelphia.com...
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eg**************@TK2MSFTNGP11.phx.gbl...
> 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]" <re******@NOyahoo.SPAMcom> wrote in message
>> news:%2****************@TK2MSFTNGP10.phx.gbl...
>>> 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.
>
>



Dec 21 '05 #11

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

Bob Lehmann

"Jeff" <gi*****@adelphia.net> wrote in message
news:W-********************@adelphia.com...

"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:OA**************@TK2MSFTNGP15.phx.gbl...
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" <gi*****@adelphia.net> wrote in message
news:l-********************@adelphia.com...
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eg**************@TK2MSFTNGP11.phx.gbl...
> 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]" <re******@NOyahoo.SPAMcom> wrote in message
>> news:%2****************@TK2MSFTNGP10.phx.gbl...
>>> 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.
>
>



Dec 21 '05 #12

P: n/a
>> 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" <gi*****@adelphia.net> wrote in message
news:Zq******************************@adelphia.com ...
But this still Doesn't answer why it is going out of sort 2/3 of the way
down, does it??

Jeff
"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:OA**************@TK2MSFTNGP15.phx.gbl...
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" <gi*****@adelphia.net> wrote in message
news:l-********************@adelphia.com...
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eg**************@TK2MSFTNGP11.phx.gbl...
> 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]" <re******@NOyahoo.SPAMcom> wrote in message
>> news:%2****************@TK2MSFTNGP10.phx.gbl...
>>> 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.
>
>



Dec 21 '05 #13

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


"Jeff" <gi*****@adelphia.net> wrote in message
news:Zq******************************@adelphia.com ...
But this still Doesn't answer why it is going out of sort 2/3 of the way
down, does it??

Jeff
"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:OA**************@TK2MSFTNGP15.phx.gbl...
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" <gi*****@adelphia.net> wrote in message
news:l-********************@adelphia.com...
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eg**************@TK2MSFTNGP11.phx.gbl...
> 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]" <re******@NOyahoo.SPAMcom> wrote in message
>> news:%2****************@TK2MSFTNGP10.phx.gbl...
>>> 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.
>
>



Dec 21 '05 #14

P: n/a
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" <gi*****@adelphia.net> wrote in message
news:Zq******************************@adelphia.com ...
But this still Doesn't answer why it is going out of sort 2/3 of the
way down, does it??

Jeff
"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:OA**************@TK2MSFTNGP15.phx.gbl...
> 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" <gi*****@adelphia.net> wrote in message
news:l-********************@adelphia.com...
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:eg**************@TK2MSFTNGP11.phx.gbl...
> 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]" <re******@NOyahoo.SPAMcom> wrote in message
>> news:%2****************@TK2MSFTNGP10.phx.gbl...
>>> 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.
Dec 21 '05 #15

P: n/a
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.
Dec 21 '05 #16

P: n/a
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:ei**************@TK2MSFTNGP14.phx.gbl...
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" <gi*****@adelphia.net> wrote in message
news:Zq******************************@adelphia.com ...
But this still Doesn't answer why it is going out of sort 2/3 of the
way down, does it??

Jeff
"Bob Lehmann" <no****@dontbotherme.zzz> wrote in message
news:OA**************@TK2MSFTNGP15.phx.gbl...
>> 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" <gi*****@adelphia.net> wrote in message
news:l-********************@adelphia.com...
> 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]" <re******@NOyahoo.SPAMcom> wrote in message
> news:eg**************@TK2MSFTNGP11.phx.gbl...
>> 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]" <re******@NOyahoo.SPAMcom> wrote in message
>>> news:%2****************@TK2MSFTNGP10.phx.gbl...
>>>> 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.

Dec 21 '05 #17

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

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uL**************@TK2MSFTNGP09.phx.gbl...
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.

Dec 21 '05 #18

P: n/a
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]" <re******@NOyahoo.SPAMcom>
wrote in message news:uL**************@TK2MSFTNGP09.phx.gbl...
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.

Dec 21 '05 #19

P: n/a
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"
Dec 21 '05 #20

P: n/a
Why not? You're simply replacing a recordset loop with an array loop.

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

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uL**************@TK2MSFTNGP09.phx.gbl...
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.


--
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"
Dec 21 '05 #21

P: n/a
Yep. I failed to hold the shift key down while typing the open parenthesis
and got a "9" instead. The statement should be:

for i=0 to ubound(arData,2)

not

for i=0 to ubound9arData,2)

Jeff wrote:
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]"
<re******@NOyahoo.SPAMcom> wrote in message
news:uL**************@TK2MSFTNGP09.phx.gbl...
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.


--
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"
Dec 21 '05 #22

P: n/a
the one that you wrote.. i put it IN Access... and when I add that to the
end, and go to the table view.. it prompts me.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
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"

Dec 21 '05 #23

P: n/a
Jeff wrote:
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:


Oh, wait, I think i know what the issue is. You tried to do this:

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
FROM members as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username
ORDER BY TotalWins DESC

and Access balked, right?

There are two options:

1. repeat the calculation in the ORDER BY:

....
ORDER BY SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
2. Use a subquery:
SELECT top 50 username, TotalMatches, TotalWins,
TotalExtraWins, TotalLoses, TotalExtraLosses, WinPercent FROM
(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
FROM members as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username) as q
ORDER BY TotalWins DESC

As to why the sort order is not correct, are you confusing TotalWins (where
extra_match = 0) with total wins (regular wins plus extra match wins)?

Bob barrows

--
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"
Dec 21 '05 #24

P: n/a
Bob, what I ended up doing, and it worked for me, is adding to the
statement.
I added
(SUM(IIf([outcome]='Win' And [extra_match]=0,1,0))+SUM(IIf([outcome]='Win'
And [extra_match]=1,1,0))) AS TotalWinsAll

That way, I could sort by that, and it does come out right.

Thanks a bunch

"Jeff" <gi*****@adelphia.net> wrote in message
news:y4********************@adelphia.com...
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]" <re******@NOyahoo.SPAMcom>
wrote in message news:uL**************@TK2MSFTNGP09.phx.gbl...
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.


Dec 22 '05 #25

P: n/a
One more thing on this please Bob,
If I wanted to narrow it down more, and add WHERE member_status = 'Active'
this would be the table that you got the username out of to begin with.
would it go something like

FROM members as mb where mb.member_status = 'Active' left join matches as m
ON mb.username= m.username
GROUP BY mb.username
"Jeff" <gi*****@adelphia.net> wrote in message
news:zo********************@adelphia.com...
Bob, what I ended up doing, and it worked for me, is adding to the
statement.
I added
(SUM(IIf([outcome]='Win' And [extra_match]=0,1,0))+SUM(IIf([outcome]='Win'
And [extra_match]=1,1,0))) AS TotalWinsAll

That way, I could sort by that, and it does come out right.

Thanks a bunch

"Jeff" <gi*****@adelphia.net> wrote in message
news:y4********************@adelphia.com...
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]" <re******@NOyahoo.SPAMcom>
wrote in message news:uL**************@TK2MSFTNGP09.phx.gbl...
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.



Dec 22 '05 #26

P: n/a
Jeff wrote:
One more thing on this please Bob,
If I wanted to narrow it down more, and add WHERE member_status =
'Active' this would be the table that you got the username out of to
begin with. would it go something like

FROM members as mb where mb.member_status = 'Active' left join
matches as m ON mb.username= m.username
GROUP BY mb.username

No. The WHERE clause always follows the FROM clause (which contains the ON
subclauses)

FROM members as mb left join matches as m
ON mb.username= m.username
where mb.member_status = 'Active'
GROUP BY mb.username

Be careful if you use the Access Query Builder Design View to add this
condition. If you simply add the member_status field to the grid and put
'Active' in the Criteria row below it, Access will put the condition in the
HAVING clause. This will be very inefficient because this will tell Jet to
aggregate all the rows FIRST, and then filter out the resulting records that
don't meet the criteria. To force the criterion to be put in the WHERE
clause where it belongs, you need to add the field to the grid, and then
change the entry in the Total row to "Where" (using the dropdown selection).

In SQL Server, you could further optimize this query by putting the
criterion in the ON clause, like this:
FROM members as mb left join matches as m
ON mb.username= m.username and mb.member_status = 'Active'
GROUP BY mb.username

But Jet will not like this syntax. You can get around this limitation by
using a subquery (or a saved query):

FROM
(select <list of fields> from members where member_status = 'Active')
as mb left join matches as m
ON mb.username= m.username
GROUP BY mb.username

The idea is to put your search conditions as early into the process as
possible to minimize the number of records that need to be dealt with by the
query engine.

Here is a post from Joe Celko that may help you understand the inner
workings (or further confuse you <grin>):
http://groups-beta.google.com/group/...194ab2109662c8

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.
Dec 22 '05 #27

This discussion thread is closed

Replies have been disabled for this discussion.