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_matc h" 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
26  2161 
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_matc h" 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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 TotalExtraLosse s
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.usernam e) 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 TotalExtraLosse s,
(SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
count(m.usernam e) as WinPercent
<snip> I know this is a lot,
But any help would be appreciated. I know basic SQL, but I don't know
inner and outer joins.
:-)
That IS "basic SQL" ...
It's the GROUP BY and the use of Iif that brings this into the "advanced"
realm. :-)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP10.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_matc h" 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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 TotalExtraLosse s
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.usernam e) 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 TotalExtraLosse s,
(SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
count(m.usernam e) 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
Now if I wanted to sort by TotalWins, would I just add that in before the
GROUP BY??
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP10.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_matc h" 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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 TotalExtraLosse s
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.usernam e) 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 TotalExtraLosse s,
(SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0)) +
SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
count(m.usernam e) as WinPercent
<snip> I know this is a lot,
But any help would be appreciated. I know basic SQL, but I don't know
inner and outer joins.
:-)
That IS "basic SQL" ...
It's the GROUP BY and the use of Iif that brings this into the "advanced"
realm. :-)
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jeff 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.connect ion")
cn.open "provider=micro soft.jet.oledb. 4.0;data source=..."
set rs=createobject ("adodb.records et")
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.
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******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP10.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_matc h" 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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
TotalExtraLosse s
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.usernam e) 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
TotalExtraLosse s, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
+ SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
count(m.usernam e) 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!!
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:eg******** ******@TK2MSFTN GP11.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******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP10.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_matc h" 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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
TotalExtraLosse s
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.usernam e) 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
TotalExtraLosse s, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
+ SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
count(m.usernam e) 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("s elect top 50 * from final order by TotalWins
DESC")
numRealPos = 0
numDisplayPos = 1
do while not admin5.eof
var1 = admin5.fields.i tem("username") .value
var2 = admin5.fields.i tem("TotalMatch es").value
var3 = admin5.fields.i tem("TotalWins" ).value
var4 = admin5.fields.i tem("TotalExtra WIns").value
var5 = admin5.fields.i tem("TotalLosse s").value
var6 = admin5.fields.i tem("TotalExtra Losses").value
var7 = admin5.fields.i tem("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(va r13,0)
numRealPos = numRealPos + 1
If Not var1 = numLastValue Then
numLastValue = var3
numDisplayPos = numRealPos
End If
%>
<tr>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=numD isplayPos%>&nbs p;</b></td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var1 %> </b></td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var8 %> - <%=var9%> </b></td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var1 4%> %</b> </td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var1 2%> </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******@NOyah oo.SPAMcom> wrote in message
news:eg******** ******@TK2MSFTN GP11.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******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP10.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_matc h" 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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
TotalExtraLosse s
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.usernam e) 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
TotalExtraLosse s, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
+ SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
count(m.usernam e) 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 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*****@adelph ia.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("s elect top 50 * from final order by TotalWins
DESC")
numRealPos = 0
numDisplayPos = 1
do while not admin5.eof
var1 = admin5.fields.i tem("username") .value
var2 = admin5.fields.i tem("TotalMatch es").value
var3 = admin5.fields.i tem("TotalWins" ).value
var4 = admin5.fields.i tem("TotalExtra WIns").value
var5 = admin5.fields.i tem("TotalLosse s").value
var6 = admin5.fields.i tem("TotalExtra Losses").value
var7 = admin5.fields.i tem("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(va r13,0)
numRealPos = numRealPos + 1
If Not var1 = numLastValue Then
numLastValue = var3
numDisplayPos = numRealPos
End If
%>
<tr>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=numD isplayPos%>&nbs p;</b></td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var1 %> </b></td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var8 %> - <%=var9%> </b></td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var1 4%> %</b> </td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var1 2%> </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******@NOyah oo.SPAMcom> wrote in message
news:eg******** ******@TK2MSFTN GP11.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******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP10.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_matc h" 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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
TotalExtraLosse s
> 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.usernam e) 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
TotalExtraLosse s, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
+ SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
count(m.usernam e) 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" <no****@dontbot herme.zzz> wrote in message
news:OA******** ******@TK2MSFTN GP15.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*****@adelph ia.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("s elect top 50 * from final order by TotalWins
DESC")
numRealPos = 0
numDisplayPos = 1
do while not admin5.eof
var1 = admin5.fields.i tem("username") .value
var2 = admin5.fields.i tem("TotalMatch es").value
var3 = admin5.fields.i tem("TotalWins" ).value
var4 = admin5.fields.i tem("TotalExtra WIns").value
var5 = admin5.fields.i tem("TotalLosse s").value
var6 = admin5.fields.i tem("TotalExtra Losses").value
var7 = admin5.fields.i tem("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(va r13,0)
numRealPos = numRealPos + 1
If Not var1 = numLastValue Then
numLastValue = var3
numDisplayPos = numRealPos
End If
%>
<tr>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=numD isplayPos%>&nbs p;</b></td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var1 %> </b></td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var8 %> - <%=var9%> </b></td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var1 4%> %</b> </td>
<td width="2%" bgcolor="#FFFFF F" align="center" style="border-style:
solid; border-width: 1px"><b><%=var1 2%> </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******@NOyah oo.SPAMcom> wrote in message
news:eg******** ******@TK2MSFTN GP11.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******@NOyah oo.SPAMcom> wrote in message
>> news:%2******** ********@TK2MSF TNGP10.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_matc h" 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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.usernam e) 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
>>> TotalExtraLosse s
>>>
>>>
>>>> 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.usernam e) 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
>>> TotalExtraLosse s, (SUM(Iif([outcome]='Win' AND [extra_match]=0,1,0))
>>> + SUM(Iif([outcome]='Win' AND [extra_match]=1,1,0)) )/
>>> count(m.usernam e) 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.
>
>
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Ike |
last post by:
Oh I have a nasty query which runs incredibly slowly. I am running MySQL
4.0.20-standard. Thus, in trying to expedite the query, I am trying to set
indexes in my tables.
My query requires four inner joins, as follows :
SELECT DISTINCT
upcards.id,statuskey.status,upcards.firstname,upcards.lastname,originkey.ori
gin,associatekey.username,associatekey2.username,upcards.deleted
FROM upcards,status,origins,associates
INNER JOIN status...
|
by: Prem |
last post by:
Hi,
I am having many problems with inner join. my first problem is :
1) I want to know the precedance while evaluating query with multiple
joins.
eg.
select Employees.FirstName, Employees.LastName, TerritoryID,
Employees.EmployeeID,
RegionID, ProductID
from Employees
|
by: jqq |
last post by:
SQL2K on W2Kserver
I need some help revamping a rather complicated query. I've given the
table and existing query information below. (FYI, changing the
database structure is right out.)
The current query lists addresses with two particular types
('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check each
contact for address type 'AM39DK3KD9' and then (2) if the contact has
type 'AM39DK3KD9' select types ('AM39DK3KD9',...
|
by: gimme_this_gimme_that |
last post by:
Can this statement be translated into DB2 ?
CREATE VIEW RELEASE_REPORTS AS (SELECT RI.GROUP_ID AS GROUP_ID,
RI.RELEASE_ID AS RELEASE_ID,
RI.TARGET_REL_ID, RI.REL_DROP, B.DROPCOUNT ,
DECODE(RI.REL_DROP, NULL , B.DROPCOUNT ) AS RCOUNT,
RI.CREATED_DATE AS DATE_ENTERED,
RI.QA_TARGET_DATE AS DATE_SCH_QA,
RI.TARGET_DATE AS DATE_SCH_PRD, Y.READY_FOR_QA,
|
by: dmonroe |
last post by:
hi group --
Im having a nested inner join problem with an Access SQl
statement/Query design. Im running the query from ASP and not usng the
access interface at all. Here's the tables:
tblEmployees
empId -- EmpName -- EmpRole -- EmpManager
-------....------------.... ---------....---------------
1........ dan yella..........1..........2
| | |
by: MP |
last post by:
Hi
trying to begin to learn database using vb6, ado/adox, mdb format, sql
(not using access...just mdb format via ado)
i need to group the values of multiple fields
- get their possible variations(combination of fields),
- then act on each group in some way ...eg ProcessRs (oRs as RecordSet)...
the following query will get me the distinct groups
|
by: Chamnap |
last post by:
Hello, everyone
I have one question about the standard join and inner join, which one
is faster and more reliable? Can you recommend me to use? Please,
explain me...
Thanks
Chamnap
|
by: teneesh |
last post by:
Here I have a code for a view that has been created by a developer on my team. I am trying to use the very same code to create a view for a different formid/quesid. But I cannot figure out how this one starts and ends. can someone please help.
here's the code from the developer.
SELECT a.EvalRecNo, w1.q1, w2.q2, w3.q3, w4.q4, w5.q5, w6.comment
FROM (SELECT DISTINCT u.EvalRecNo
FROM dbo.UData AS u...
|
by: MATTXtwo |
last post by:
I have this store procedure to select data from table with join like this...SELECT tblPeribadi.Personel_No, tblPeribadi.Nama,tblCompany.Keterangan as Company_Code, tblPeribadi.Jawatan, tblPeribadi.Taraf_Jawatan,
tblGroup.Keterangan AS Kumpulan,tblPeribadi.Gred,tblBusiness_Area.Keterangan AS Business_Area,tblCost_Center.Keterangan AS Kod_Pusat_Kos, tblPeribadi.IC_Baru, tblPeribadi.IC_Lama,
...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
