Query Troubles

Results 1 to 3 of 3

Thread: Query Troubles

  1. #1
    Steve Ward Guest

    Default Query Troubles

    Hi All.<BR>I have been racking my head on this for days now, and with help from message board here, i have a better understanding how this works and am real close to getting it right.<BR>Please help to get this right if you can.<BR>Acess2000 DB<BR>Table [Reports]<BR>Field&#039;s ID(AutoNum), Tourney, Round, Team, Win, Loss<BR>ie: of input Field/Values to be inserted<BR>Tourney,1, Round,4, Team,Bears, Win,Win, <BR><BR>Each tourney has 4 Rounds 1,2,3,4. Each team enters a win or a loss and can be many, within each round.<BR><BR>For each Round i want to show only the Team Name and highest score based on the calculation (Win*5+Loss) BUT i want to show the winner of all Rounds. (or group by if you wish)<BR>T1, R1, Team Bears, 120 points<BR>T1, R2, Team Skins, 160 Points<BR>T1, R3, Team Diver, 90 points<BR><BR>The below statement does it like this (not what i want tho)<BR>T1, R1, Team Bears, 120 points<BR>T1, R1, Team Skins, 160 Points<BR>T1, R1, Team Diver, 90 points<BR>T1, R2, Team Bears, 100 points<BR>T1, R2, Team Skins, 60 Points<BR>T1, R2, Team Diver, 200 points<BR>ETC..<BR><BR>This statement is best i got so far.<BR>Dim sql<BR>sql = "Select Tourney, Round, Team, TotalScore From (Select Tourney, Round, Team, (Count(Win)*5 + Count(Loss)) As TotalScore From Reports Group By Tourney, Round, Team ) Where Tourney = 1 Order By Round, TotalScore Desc "<BR>Conn.Execute(sql)<BR><BR>This works but<BR>It shows all the teams in each round. with the correct points. But im looking for only the Highest Score for each round.<BR><BR>I have tried many different variations of Max(Count(Win)*5+Count(Loss)) but get errors.<BR><BR>I really have tried, and do now have a better understanding of how Select statement within a select statement works. but this one in particular is beyond me, and has me all beatup.<BR><BR>Would appreciate more help if you can.<BR>Thanks all in advance.<BR><BR>Steve Ward<BR>

  2. #2
    DaveL Guest

    Default RE: Query Troubles

    Have you tried the TOP operator?

  3. #3
    Steve Ward Guest

    Default RE: Query Troubles

    Yes tried top operator.<BR>Doesnt matter where i put Select Top 1 insite of the 2 statements.<BR>It will only return 1 value only.<BR>Im looking to pull 1 value for each of the 4 rounds.<BR>I could make 4 different queries using where for each round.<BR>ie. <BR>sql = Select Count(Win)*5 + Count(Loss) As Total Where Tourney = 1 AND Round =1<BR><BR>sql2 = Select Count(Win)*5 + Count(Loss) As Total Where Tourney = 1 AND Round =2<BR><BR>sql3 = Select Count(Win)*5 + Count(Loss) As Total Where Tourney = 1 AND Round =3<BR><BR>sql4 = Select Count(Win)*5 + Count(Loss) As Total Where Tourney = 1 AND Round =4<BR><BR>To run 4 different Querries and have 4 different connections i thought would not be too efficient.<BR><BR>If that&#039;s what i have to do then i&#039;ll do it.<BR>But would prefer something more efficient and clean.<BR>Should be able to do it.<BR><BR>Thanks all<BR><BR>Steve Ward

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts