|
it
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Complicated inner join??
Using Access DB I have a table in my DB called members. In that table, I have 2 tables I will be using "username" and "points" Now, I also have a table called all_matches. This table contains every match report. Over 25,000 of them. I have a "username" field an "outcome" field an "username1" field and "extra_match" field. What I need to do, is make a query that takes the username from table "members" and find : using the "username" field in the members table, i need to obtain stats that give me the following. 1.) how many total matches played. Which would simply be a count of the username field in the all_matches table. Since people report the match win or lose. 2.) how many wins, where extra_match = 0. This would be a count of username in the all_matches table where "outcome" = 'Win' and extra_match=0. 3.) how many wins, where extra_match = 1. This would be a count of username in the all_matches table where "outcome" = 'Win' and extra_match=1 4.) how many losses, where extra_match = 0. This would be a count of username in the all_matches table where "outcome" = 'Lose' and extra_match=0.. 5.) how many losses, where extra_match = 1. This would be a count of username in the all_matches table where "outcome" = 'Lose' and extra_match=1. 6.) Then divide the wins into the total games and get a win % now I did this, using top 50 so it wasn't too intensive, since there are so many lines in the all_matches table. But what I can't do is get the sort right. But I think if i can do this in access, instead of doing it on an asp page, it will be easier. I had one that was done for me on another DB, but I was unable to modify it for this DB. I can show you the script of what I did on the asp page, but it is rather sloppy, and it doesn't work anyway. So I need help creating this SQL within access as a query. I know this is a lot, But any help would be appreciated. I know basic SQL, but I don't know inner and outer joins. THanks bam Jeff wrote:
> Ok gang. Here is something complicated, well, at least to me anyway. Please include datatypes in your descriptions of your tables> 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" Table: Members Columns: username Text points Number? Long? Single? > Same deal. What is the purpose of "username1"?> 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. > SELECT mb.username, count(m.username) as TotalMatches> 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. 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 <the FROM and GROUP clauses will not change, so I will not repeat then>> username in the all_matches table where "outcome" = 'Win' and > extra_match=0. 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 SELECT mb.username, count(m.username) as TotalMatches,> username in the all_matches table where "outcome" = 'Win' and > extra_match=1 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 SELECT mb.username, count(m.username) as TotalMatches,> username in the all_matches table where "outcome" = 'Lose' and > extra_match=0.. 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 SELECT mb.username, count(m.username) as TotalMatches,> username in the all_matches table where "outcome" = 'Lose' and > extra_match=1. 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, That IS "basic SQL" ...> But any help would be appreciated. I know basic SQL, but I don't know > inner and outer joins. :-) It's the GROUP BY and the use of Iif that brings this into the "advanced" realm. :-) Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup. "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message ok. username is 55 ch text, points is long integer.news:%23njk9qZBGHA.3488@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? > the purpose of username1 is that the person reporting is the username... the >> >> 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"? 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. Show quote > yes, TotalWins would be the total from extra_match = 0 and extra_match = 1.>> >> 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: Show quote > SELECT mb.username, count(m.username) as TotalMatches, Ok, so I need to put all these into the sql in access. Hopefully nothing > 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. > > changed with the info I gave after your questions. In all actuality, the username1 shouldn't even come into play for this I don't think. Thanks Bob Jeff Jeff 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 itGetUserStats 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. Now if I wanted to sort by TotalWins, would I just add that in before the
GROUP BY?? Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:%23njk9qZBGHA.3488@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. > > No. After. The ORDER BY always comes last. (The Access Query Builder allows
you to answer these types of questions yourself) Jeff wrote: Show quote > Now if I wanted to sort by TotalWins, would I just add that in before > the GROUP BY?? > > > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message > news:%23njk9qZBGHA.3488@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. Thanks Bob, I actually did a build in access with this query, then call it
from the asp page. YOUDAMAN!! Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:egNwthaBGHA.3580@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]" <reb01501@NOyahoo.SPAMcom> wrote in message >> news:%23njk9qZBGHA.3488@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. > > 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%> </b></td> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: solid; border-width: 1px"><b><%=var1%> </b></td> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: solid; border-width: 1px"><b><%=var8%> - <%=var9%> </b></td> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: solid; border-width: 1px"><b><%=var14%> %</b> </td> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: solid; border-width: 1px"><b><%=var12%> </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 Show quote "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:egNwthaBGHA.3580@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]" <reb01501@NOyahoo.SPAMcom> wrote in message >> news:%23njk9qZBGHA.3488@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. > > >> First, it is displaying 51 records. That is because you have a tie somewhere. For example, if you had1,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 Show quote "Jeff" <gig_***@adelphia.net> wrote in message news:l-ednWeYmZttCDXe4p2dnA@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%> </b></td> > <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: > solid; border-width: 1px"><b><%=var1%> </b></td> > <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: > solid; border-width: 1px"><b><%=var8%> - <%=var9%> </b></td> > <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: > solid; border-width: 1px"><b><%=var14%> %</b> </td> > <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: > solid; border-width: 1px"><b><%=var12%> </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]" <reb01501@NOyahoo.SPAMcom> wrote in message > news:egNwthaBGHA.3580@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]" <reb01501@NOyahoo.SPAMcom> wrote in message > >> news:%23njk9qZBGHA.3488@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. > > > > > > "Bob Lehmann" <nospam@dontbotherme.zzz> wrote in message I thought that might be the reason actually.news:OABl44cBGHA.3840@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. > Yes, TotalWins IS a field.>>> It prompted me for a Parameter. > Is "TotalWins" a column in your table? > I am doing that because I do want all fields that are created in the SQL > PS - Quit using "*" for your SELECT. Explicitly name the columns you want that is in access. Is this still wrong?? Show quote > returned. > > Bob Lehmann > > "Jeff" <gig_***@adelphia.net> wrote in message > news:l-ednWeYmZttCDXe4p2dnA@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%> </b></td> >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >> solid; border-width: 1px"><b><%=var1%> </b></td> >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >> solid; border-width: 1px"><b><%=var8%> - <%=var9%> </b></td> >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >> solid; border-width: 1px"><b><%=var14%> %</b> </td> >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >> solid; border-width: 1px"><b><%=var12%> </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]" <reb01501@NOyahoo.SPAMcom> wrote in message >> news:egNwthaBGHA.3580@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]" <reb01501@NOyahoo.SPAMcom> wrote in message >> >> news:%23njk9qZBGHA.3488@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. >> > >> > >> >> > > >> I do want all fields that are created in the SQL that is in access. Is this still wrong??Yes. Bob Lehmann Show quote "Jeff" <gig_***@adelphia.net> wrote in message style="border-style:news:W-mdnSTp27mnKDXeRVn-gw@adelphia.com... > > "Bob Lehmann" <nospam@dontbotherme.zzz> wrote in message > news:OABl44cBGHA.3840@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" <gig_***@adelphia.net> wrote in message > > news:l-ednWeYmZttCDXe4p2dnA@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" > >> solid; border-width: 1px"><b><%=numDisplayPos%> </b></td> style="border-style:> >> <td width="2%" bgcolor="#FFFFFF" align="center" > >> solid; border-width: 1px"><b><%=var1%> </b></td> style="border-style:> >> <td width="2%" bgcolor="#FFFFFF" align="center" > >> solid; border-width: 1px"><b><%=var8%> - <%=var9%> </b></td> style="border-style:> >> <td width="2%" bgcolor="#FFFFFF" align="center" > >> solid; border-width: 1px"><b><%=var14%> %</b> </td> style="border-style:> >> <td width="2%" bgcolor="#FFFFFF" align="center" Show quote > >> solid; border-width: 1px"><b><%=var12%> </b></td> [extra_match]=0,1,0))> >> </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]" <reb01501@NOyahoo.SPAMcom> wrote in message > >> news:egNwthaBGHA.3580@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]" <reb01501@NOyahoo.SPAMcom> wrote in message > >> >> news:%23njk9qZBGHA.3488@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 Show quote > >> >>> + 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. > >> > > >> > > >> > >> > > > > > > But this still Doesn't answer why it is going out of sort 2/3 of the way
down, does it?? Jeff Show quote "Bob Lehmann" <nospam@dontbotherme.zzz> wrote in message news:OABl44cBGHA.3840@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" <gig_***@adelphia.net> wrote in message > news:l-ednWeYmZttCDXe4p2dnA@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%> </b></td> >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >> solid; border-width: 1px"><b><%=var1%> </b></td> >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >> solid; border-width: 1px"><b><%=var8%> - <%=var9%> </b></td> >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >> solid; border-width: 1px"><b><%=var14%> %</b> </td> >> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >> solid; border-width: 1px"><b><%=var12%> </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]" <reb01501@NOyahoo.SPAMcom> wrote in message >> news:egNwthaBGHA.3580@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]" <reb01501@NOyahoo.SPAMcom> wrote in message >> >> news:%23njk9qZBGHA.3488@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. >> > >> > >> >> > > >> 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 Show quote "Jeff" <gig_***@adelphia.net> wrote in message style="border-style:news:ZqSdneU5ZoJZ1DTenZ2dnUVZ_tudnZ2d@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" <nospam@dontbotherme.zzz> wrote in message > news:OABl44cBGHA.3840@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" <gig_***@adelphia.net> wrote in message > > news:l-ednWeYmZttCDXe4p2dnA@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" > >> solid; border-width: 1px"><b><%=numDisplayPos%> </b></td> style="border-style:> >> <td width="2%" bgcolor="#FFFFFF" align="center" > >> solid; border-width: 1px"><b><%=var1%> </b></td> style="border-style:> >> <td width="2%" bgcolor="#FFFFFF" align="center" > >> solid; border-width: 1px"><b><%=var8%> - <%=var9%> </b></td> style="border-style:> >> <td width="2%" bgcolor="#FFFFFF" align="center" > >> solid; border-width: 1px"><b><%=var14%> %</b> </td> style="border-style:> >> <td width="2%" bgcolor="#FFFFFF" align="center" Show quote > >> solid; border-width: 1px"><b><%=var12%> </b></td> [extra_match]=0,1,0))> >> </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]" <reb01501@NOyahoo.SPAMcom> wrote in message > >> news:egNwthaBGHA.3580@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]" <reb01501@NOyahoo.SPAMcom> wrote in message > >> >> news:%23njk9qZBGHA.3488@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 Show quote > >> >>> + 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. > >> > > >> > > >> > >> > > > > > > Waiting for Bob Barrows {MVP} to reply, since he is the one who helped me to
begin with. Show quote "Jeff" <gig_***@adelphia.net> wrote in message news:ZqSdneU5ZoJZ1DTenZ2dnUVZ_tudnZ2d@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" <nospam@dontbotherme.zzz> wrote in message > news:OABl44cBGHA.3840@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" <gig_***@adelphia.net> wrote in message >> news:l-ednWeYmZttCDXe4p2dnA@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%> </b></td> >>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >>> solid; border-width: 1px"><b><%=var1%> </b></td> >>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >>> solid; border-width: 1px"><b><%=var8%> - <%=var9%> </b></td> >>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >>> solid; border-width: 1px"><b><%=var14%> %</b> </td> >>> <td width="2%" bgcolor="#FFFFFF" align="center" style="border-style: >>> solid; border-width: 1px"><b><%=var12%> </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]" <reb01501@NOyahoo.SPAMcom> wrote in message >>> news:egNwthaBGHA.3580@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]" <reb01501@NOyahoo.SPAMcom> wrote in message >>> >> news:%23njk9qZBGHA.3488@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. >>> > >>> > >>> >>> >> >> > > When you run this query in Access, do you get similar results? Jeff wrote: Show quote > Waiting for Bob Barrows {MVP} to reply, since he is the one who > helped me to begin with. > > > > > "Jeff" <gig_***@adelphia.net> wrote in message > news:ZqSdneU5ZoJZ1DTenZ2dnUVZ_tudnZ2d@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" <nospam@dontbotherme.zzz> wrote in message >> news:OABl44cBGHA.3840@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" <gig_***@adelphia.net> wrote in message >>> news:l-ednWeYmZttCDXe4p2dnA@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%> </b></td> <td width="2%" >>>> bgcolor="#FFFFFF" align="center" style="border-style: solid; >>>> border-width: 1px"><b><%=var1%> </b></td> <td width="2%" >>>> bgcolor="#FFFFFF" align="center" style="border-style: solid; >>>> border-width: 1px"><b><% | |||||||||||||||||||||||
