Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: design and results of query

    hi,

    i have a golf scorecard vb6 app i am developing. underneath is a database consisting of 5 tables. please find an image of the relationships <a href="http://homepage.ntlworld.com/daniel.burt/relationships.jpg">here </a>.

    essentially, my query is mainly based upon a couple of tables (others are tied in, but only require data from 2 tables for what i want to do with results)

    i have a vb <a href="http://homepage.ntlworld.com/daniel.burt/vb_form.jpg">form </a> which when you select a player's name, it will then perform a search of the database and return every round that the player has played. please find a screenshot of this form. the section i am attempting to populate is the grid at the bottom.

    this is my query at present:
    sqlStr3 = "SELECT p.PlayerNickName, c.CourseName, r.RoundID, r.RoundDate, r.RoundScore, rh.RoundHoleRoundID, rh.RoundHoleScore, p.PlayerID " & _
    "FROM (players p INNER JOIN (courses c INNER JOIN rounds r ON c.CourseID = r.RoundCourseID) ON p.PlayerID = r.RoundPlayerID) INNER JOIN RoundByHole rh ON r.RoundID = rh.RoundHoleRoundID " & _
    "WHERE (((p.PlayerID)=1))"

    this returns 18 instances of "roundid" next to the score for each "roundbyhole". i only require 1 instance of this number, and then every hole will appear in the grid aswell.

    any help would be appreciated...

    thanks

    jingo_man

  2. #2
    Join Date
    Mar 2004
    Posts
    4

    Talking

    found a workaround solution....

    have got 2 recordsets. one returns the no number of rounds played and details about this round. the second is the query posted above which has details of every hole in every round played.

    i have then looped through the 1st recordset which sets the coursename, coursedate, total fields. then there is a nested loop which uses the 2nd recordset to populate each record into the grid.

    find a screenshot here:

    http://homepage.ntlworld.com/daniel.burt/screen2.jpg

    cheers,

    dan

Posting Permissions

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