Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Join query (i think)

Status
Not open for further replies.
penguinspeaks

penguinspeaks

Technical User
Nov 13, 2002
234
US
OK. this is in access DB. I have a table called matches. i have a field
called (username) and a field called (username1) this is where the reports
go to. i have another table called players, that has (username)and total_points field.

what i want to do is this... i want to display a table.. that gets the
username from the 'players' table, and match it up with the info from the
'matches' table.. so the result would show

player wins loses total_games % total_points
----------------------------------------------------
playera 6 4 10 66.6 1524

so basically.. it would count the number of times a name appeared in
the username column as wins, count the number of times the same name
appeared in the username1 column as loses and add the 2 counts together to =
total games... then it would take the wins, and divide by the total_games to
get the %
is this possible?? and does this make sense? or would there be an easier way
to do this in the DB?
Jeff
 
Sort by date Sort by votes
I don't think you have told us how to determine who is the winner. Please take the time to type or past about 10 sample records and the expected results in your "standings" query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Code: 
select p.username  as player
     , sum(iif(p.username
              =m.username,1,0)) as wins
     , sum(iif(p.username
              =m.username1,1,0)) as losses
     , count(*)              as total_games
     , sum(iif(p.username
              =m.username,1,0))
         /count(*)              as percent
     , p.total_points
  from players as p
inner
  join matches as m
    on p.username in (m.username,m.username1)
group
    by p.username
     , p.total_points


rudy
SQL Consulting
 
Upvote 0 Downvote
actually... i did determine the winner... the (username) culumn is the winner, and the (username1) column is the loser..in the matches table.... there are no ties...

i tried the example given by r937.. and get this error:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.


thanks for the help
Jeff
 
Upvote 0 Downvote
ok. i changed to what you said... and now creates a new error:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'p.username in (m.username,m.username1) group by p.username , p.total_points'.

thanks for the help
Jeff
 
Upvote 0 Downvote
okay, try changing this --
Code: 
  from players as p
inner
  join matches as m
    on p.username in (m.username,m.username1)

to this --
Code: 
  from players as p
     , matches as m
 where p.username in (m.username,m.username1)


rudy
SQL Consulting
 
Upvote 0 Downvote
ok.. here is what I have now.. and am getting the following error:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'p.username  as player      '.



select p.username  as player
     , sum(iif(p.username
              =m.username,1,0)) as wins
     , sum(iif(p.username
              =m.username1,1,0)) as losses
     , count(*)              as total_games
     , sum(iif(p.username
              =m.username,1,0))
         /count(*)              as prct
     , p.total_points
  from players as p
     , matches as m
 where p.username in (m.username, m.username1)
group
    by p.username
     , p.total_points
 
Upvote 0 Downvote
I would first create a union query (quniPlayWithMatches) to normalize Matches:
[tt][blue]
SELECT UserName, 1 AS Win
FROM Matches
UNION ALL
SELECT UserName1,0
FROM Matches;
[/blue][tt]
Then create a query based on quniPlayWithMatches and Players:
[tt][blue]
SELECT Players.UserName,
Sum(quniPlayWithMatches.Win) AS Wins,
Sum(Abs([Win]=0)) AS Losses,
Sum(1) AS Total_Games,
Sum([Win])/Count(*)*100 AS WinPct,
Players.Total_Points
FROM quniPlayWithMatches
INNER JOIN Players ON quniPlayWithMatches.UserName = Players.UserName
GROUP BY Players.UserName, Players.Total_Points;
[/blue][tt]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
ok.. when testing this in access.. i get a pop up box that asks
Enter Paramater Value for:
Players.Total_Points

no matter what i enter, it sets the total_points to that value for all the records
 
Upvote 0 Downvote
also.. what should I put there, to round the avg to 2 decimal places??
 
Upvote 0 Downvote
Bambamn007,
Are you referring to one of my queries or something from an earlier post? Do you not have a "table called players, that has (username)and total_points" as fields?

All display formatting should be done in the report or form. If you are not using one of these then set the format in the query field properties.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Yes. I do have that table and those fields in the table.. as i stated, when in access, i click the second query to view it, and i get that pop up where it wants me to enter a value. after doing so, no matter what i enter, it makes the total_points return the same result(whatever i entered) for all records.

Enter Paramater Value for:
Players.Total_Points


however, the query does work, with the exception of havint to enter that data to start it. but because of that, it won't work in my web :(
Thanks again for your help.
Jeff
 
Upvote 0 Downvote
why don't you post the exact query that you are currently running that prompts you so we can see what you are doing.

Leslie
 
Upvote 0 Downvote
on second thought, didn't you use P as an alias for players? Then you need to have P.Total_Points, not Players.Total_Points.

Leslie
 
Upvote 0 Downvote
Ok. thx for the reply, but I got it to work with some tweaking. The last step, is to format the WinPct to display in a 0.00 % format.

SELECT Players.UserName,
Sum(quniPlayWithMatches.Win) AS Wins,
Sum(Abs([Win]=0)) AS Losses,
Sum(1) AS Total_Games,
Sum([Win])/Count(*)*100 AS WinPct, <--this line to 0.00%
Players.Total_Points
FROM quniPlayWithMatches
INNER JOIN Players ON quniPlayWithMatches.UserName = Players.UserName
GROUP BY Players.UserName, Players.Total_Points;
 
Upvote 0 Downvote
BamBamn007,
I don't see a difference between what I suggested and what you just pasted into a reply.

If you are presenting this in a "web" as suggested, you should do the formatting of the pct in the web page. If I was using this query in a form or report, I would apply the format in the text box.

You can format in a query using the Format() function but I am not sure if your web-query supports the format() function.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Yes. It does support the format function. And yes, I got the sql you gave to work. not sure why it wouldn't to begin with, but is working fine now.

so can i do this?:

Sum([Win])/Count(*)*100 AS WinPct,FORMAT(WinPct) or something?
 
Upvote 0 Downvote
First.. I want to thank each and everyone of you for helping me with this. I got the format to work. I put this in there:

Sum([Win])/Count(*)*100 AS WinPct, Format(WinPct,"0.00") & "%"

And it works wonderfully.
Thanks again
Jeff
Bam
 
Upvote 0 Downvote
I would have used
, Format(Sum([Win])/Count(*)*100,"0.00") & "%" AS WinPct


Duane
MS Access MVP
Find out how to get great answers faq219-2884.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

C
  • Locked
  • Question
Query expression negative to a 0
Replies
6
Views
496
dhookom
dhookom
serino
  • Locked
  • Question
Help with a Query. 1
Replies
13
Views
985
strongm
strongm
C
  • Locked
  • Question
Run-time error '3075': Syntax error (missing operator) in query expression 'Birthday = and Birthmonth ='.
Replies
3
Views
430
OzFoxy
OzFoxy
breezett93
  • Question
Query takes 30 seconds - 1 minute to process. Trying to rework it to take 5 or less
Replies
8
Views
485
breezett93
breezett93
combo
  • Locked
  • Question
'Wrong property setting' message in query 1
Replies
2
Views
5K
combo
combo

Part and Inventory Search

Sponsor

Back
Top