PC Review


Reply
Thread Tools Rate Thread

Golf Score Reporting

 
 
=?Utf-8?B?QmFycnk=?=
Guest
Posts: n/a
 
      27th Jun 2007
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




 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      27th Jun 2007
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>
>
>
>



 
Reply With Quote
 
 
 
 
=?Utf-8?B?QmFycnk=?=
Guest
Posts: n/a
 
      28th Jun 2007
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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >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
> >
> >
> >
> >

>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Golf score & stats tracker =?Utf-8?B?ZGF2eUg=?= Microsoft Excel Misc 0 18th Apr 2006 08:11 PM
golf score templet for a league are there any =?Utf-8?B?bWF4YWxheA==?= Microsoft Excel Charting 0 1st Apr 2006 04:04 PM
How do I set up a golf score template? =?Utf-8?B?c2xwY2E=?= Microsoft Access 1 21st May 2005 11:39 PM
Golf Score Spreadsheet Donald Bruns Microsoft Excel Worksheet Functions 2 26th Feb 2004 12:09 AM
Golf score problem Harry Limey Microsoft Excel Discussion 5 7th Dec 2003 04:39 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:45 AM.