Golf Score Reporting

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
..
 
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
 
Back
Top