Golf Score Reporting

Discussion in 'Microsoft Access Reports' started by Guest, Jun 27, 2007.

  1. Guest

    Guest Guest

    I have created a database to keep track of golf scores, courses etc. It has
    come time to start creating reports and I would like to report all scores
    across the page of the report in the format hole 1, hole 2 etc. through hole
    18. The next round to be directly beneath the first for all rounds played
    for any one or more GolferID. This will be just one report to display the
    trend of scores for any golfer on one or many courses.

    The records for the rounds played are kept in two tables, tblRounds and
    tblRoundDetails. The table tblRounds contains fields RoundID, RoundDate,
    CourseID, TeeID and GolferID. The table tblRoundDetails contains the fields
    RoundID, HoleNumber and Score.

    The fields are unbound and I have been using the Tag property to identify
    them as I loop through the report. I currently get 18 seperate records for
    each RoundID, followed by 18 for the next etc. Obviously I would prefer only
    one record per round across the page. My current attempt to code this is as
    follows.

    Dim i As Integer
    Dim j As Integer
    Dim db As Database
    Dim rst As Recordset
    Dim MyReport As Report

    Set MyReport = Reports!Report1
    Set db = CurrentDb

    'Open recordset to find scores
    Set rst = db.OpenRecordset("SELECT tblRounds.RoundID,
    tblRoundDetails.HoleNumber, tblRoundDetails.Score " & _
    "FROM tblRounds INNER JOIN tblRoundDetails ON tblRounds.RoundID =
    tblRoundDetails.RoundID ")

    'Fill form with score from recordset
    If rst.RecordCount > 0 Then
    rst.MoveLast
    rst.MoveFirst

    For i = 1 To 18
    For j = 0 To MyReport.Count - 1
    If MyReport(j).Tag = "S" & i Then
    MyReport(j) = rst!Score
    End If
    Next j
    rst.MoveNext
    Next i

    End If

    Any help is greatly appreciated.

    Thanks,

    Barry
     
    Guest, Jun 27, 2007
    #1
    1. Advertisements

  2. Guest

    John Spencer Guest

    Have you looked at a cross tab query.

    TRANSFORM First(D.Score) as TheScore
    SELECT R.RoundID, R.RoundDate, R.CourseID, R.GolferID
    FROM tblRounds as R INNER JOIN tblRoundDetails as D
    On R.RoundID = D.RoundID
    GROUP BY R.RoundID, R.RoundDate, R.CourseID, R.GolferID
    PIVOT D.HoleNumber

    You may need to modify that a bit, but that should give you the idea.

    There is a crosstab wizard that can help you build the query.
    --
    John Spencer
    Access MVP 2002-2005, 2007
    Center for Health Program Development and Management
    University of Maryland Baltimore County
    ..

    "Barry" <> wrote in message
    news:...
    >I have created a database to keep track of golf scores, courses etc. It
    >has
    > come time to start creating reports and I would like to report all scores
    > across the page of the report in the format hole 1, hole 2 etc. through
    > hole
    > 18. The next round to be directly beneath the first for all rounds played
    > for any one or more GolferID. This will be just one report to display the
    > trend of scores for any golfer on one or many courses.
    >
    > The records for the rounds played are kept in two tables, tblRounds and
    > tblRoundDetails. The table tblRounds contains fields RoundID, RoundDate,
    > CourseID, TeeID and GolferID. The table tblRoundDetails contains the
    > fields
    > RoundID, HoleNumber and Score.
    >
    > The fields are unbound and I have been using the Tag property to identify
    > them as I loop through the report. I currently get 18 seperate records
    > for
    > each RoundID, followed by 18 for the next etc. Obviously I would prefer
    > only
    > one record per round across the page. My current attempt to code this is
    > as
    > follows.
    >
    > Dim i As Integer
    > Dim j As Integer
    > Dim db As Database
    > Dim rst As Recordset
    > Dim MyReport As Report
    >
    > Set MyReport = Reports!Report1
    > Set db = CurrentDb
    >
    > 'Open recordset to find scores
    > Set rst = db.OpenRecordset("SELECT tblRounds.RoundID,
    > tblRoundDetails.HoleNumber, tblRoundDetails.Score " & _
    > "FROM tblRounds INNER JOIN tblRoundDetails ON tblRounds.RoundID =
    > tblRoundDetails.RoundID ")
    >
    > 'Fill form with score from recordset
    > If rst.RecordCount > 0 Then
    > rst.MoveLast
    > rst.MoveFirst
    >
    > For i = 1 To 18
    > For j = 0 To MyReport.Count - 1
    > If MyReport(j).Tag = "S" & i Then
    > MyReport(j) = rst!Score
    > End If
    > Next j
    > rst.MoveNext
    > Next i
    >
    > End If
    >
    > Any help is greatly appreciated.
    >
    > Thanks,
    >
    > Barry
    >
    >
    >
    >
     
    John Spencer, Jun 27, 2007
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    John:
    Appears to be a fabulous idea! I made a query first with the two tables
    tblRounds and tblRoundDetails and used that as the basis of the crosstab.
    The report appears as I had hoped. This is a much cleaner and more sensible
    approach. Thanks very much for your help.
    Barry

    "John Spencer" wrote:

    > Have you looked at a cross tab query.
    >
    > TRANSFORM First(D.Score) as TheScore
    > SELECT R.RoundID, R.RoundDate, R.CourseID, R.GolferID
    > FROM tblRounds as R INNER JOIN tblRoundDetails as D
    > On R.RoundID = D.RoundID
    > GROUP BY R.RoundID, R.RoundDate, R.CourseID, R.GolferID
    > PIVOT D.HoleNumber
    >
    > You may need to modify that a bit, but that should give you the idea.
    >
    > There is a crosstab wizard that can help you build the query.
    > --
    > John Spencer
    > Access MVP 2002-2005, 2007
    > Center for Health Program Development and Management
    > University of Maryland Baltimore County
    > ..
    >
    > "Barry" <> wrote in message
    > news:...
    > >I have created a database to keep track of golf scores, courses etc. It
    > >has
    > > come time to start creating reports and I would like to report all scores
    > > across the page of the report in the format hole 1, hole 2 etc. through
    > > hole
    > > 18. The next round to be directly beneath the first for all rounds played
    > > for any one or more GolferID. This will be just one report to display the
    > > trend of scores for any golfer on one or many courses.
    > >
    > > The records for the rounds played are kept in two tables, tblRounds and
    > > tblRoundDetails. The table tblRounds contains fields RoundID, RoundDate,
    > > CourseID, TeeID and GolferID. The table tblRoundDetails contains the
    > > fields
    > > RoundID, HoleNumber and Score.
    > >
    > > The fields are unbound and I have been using the Tag property to identify
    > > them as I loop through the report. I currently get 18 seperate records
    > > for
    > > each RoundID, followed by 18 for the next etc. Obviously I would prefer
    > > only
    > > one record per round across the page. My current attempt to code this is
    > > as
    > > follows.
    > >
    > > Dim i As Integer
    > > Dim j As Integer
    > > Dim db As Database
    > > Dim rst As Recordset
    > > Dim MyReport As Report
    > >
    > > Set MyReport = Reports!Report1
    > > Set db = CurrentDb
    > >
    > > 'Open recordset to find scores
    > > Set rst = db.OpenRecordset("SELECT tblRounds.RoundID,
    > > tblRoundDetails.HoleNumber, tblRoundDetails.Score " & _
    > > "FROM tblRounds INNER JOIN tblRoundDetails ON tblRounds.RoundID =
    > > tblRoundDetails.RoundID ")
    > >
    > > 'Fill form with score from recordset
    > > If rst.RecordCount > 0 Then
    > > rst.MoveLast
    > > rst.MoveFirst
    > >
    > > For i = 1 To 18
    > > For j = 0 To MyReport.Count - 1
    > > If MyReport(j).Tag = "S" & i Then
    > > MyReport(j) = rst!Score
    > > End If
    > > Next j
    > > rst.MoveNext
    > > Next i
    > >
    > > End If
    > >
    > > Any help is greatly appreciated.
    > >
    > > Thanks,
    > >
    > > Barry
    > >
    > >
    > >
    > >

    >
    >
    >
     
    Guest, Jun 28, 2007
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tomas Tokman

    OT: New reporting tool

    Tomas Tokman, Jul 10, 2003, in forum: Microsoft Access Reports
    Replies:
    0
    Views:
    428
    Tomas Tokman
    Jul 10, 2003
  2. Steve

    Print reporting period on a report header

    Steve, Jul 15, 2003, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    406
    Steve
    Jul 15, 2003
  3. Rajesh Tuliani

    Static reporting (no refresh on reruns)

    Rajesh Tuliani, Jul 18, 2003, in forum: Microsoft Access Reports
    Replies:
    1
    Views:
    428
  4. ET SHERMAN

    reporting with multiple fields

    ET SHERMAN, Jul 28, 2003, in forum: Microsoft Access Reports
    Replies:
    0
    Views:
    212
    ET SHERMAN
    Jul 28, 2003
  5. Guest

    Finding the lowest score per student

    Guest, Oct 27, 2006, in forum: Microsoft Access Reports
    Replies:
    3
    Views:
    186
    Guest
    Oct 28, 2006
Loading...

Share This Page