Report based on table with variable number of fields 1

[MeldrethMan]

I'm reporting players scores over a number of weeks. Each week I generate a list of each players scores and append them with round number onto tblScores. I then pick out the players' best N scores over a season, say best N qualifier scores out of however many rounds they play.

I'd like to make my reporting variable so selecting N gives a league report with the required number of fields.

This creates a temp table with Player, Round No and Points

'Player loop, count players
sqltxt = "Select Distinct Player From tblScores"
Set rN = CurrentDb.OpenRecordset(sqltxt)

'POINTS LOOP
Do Until rN.EOF

sqltxt = "SELECT TOP " & Qualifiers & " tblScores.Player, tblCumulativeScores.TotalPoints FROM tblScores " _
& "WHERE (((tblScores.Player) = '" & rN!Player & "')) ORDER BY tblScores.TotalPoints DESC;"

Set rP = CurrentDb.OpenRecordset(sqltxt)
n = 1
Do Until rP.EOF
sqltxt = "Insert Into Temp(Player, TotalPoints, Round) Values(" & Chr(34) & rP!Player & Chr(34) & ", " & rP!TotalPoints & ", " & n & ")"

DoCmd.RunSQL (sqltxt)
Debug.Print sqltxt

'this ensures that only required number of records are returned
n = n + 1
If n = Qualifiers + 1 Then Exit Do
rP.MoveNext
Loop
rP.Close
Set rP = Nothing
rN.MoveNext
Loop

rN.Close
Set rN = Nothing

This crosstab query lists players and their N best scores

TRANSFORM Sum(temp.TotalPoints) AS SumOfTotalPoints
SELECT temp.Player, Sum(temp.TotalPoints) AS Total
FROM temp
GROUP BY temp.Player
PIVOT temp.Round;

This displays the results but the format is obviously very basic.

I'd like to use this as the source for a report but can't see how to accommodate the fact that the number of fields may vary depending what N we choose.

 

[dhookom]

Have you considered set the column headings property of the cross tab with a max number so you can more easily build the report with known columns?

Duane
Hook'D on Access
MS Access MVP

 

[MeldrethMan]

Thanks Duane but struggling with this, sorry. I searched column headings and got to Allen Browne's website which had a reference link to your Hook'D site, but I didn't get much further. How do the headings work?

 

[dhookom]

Crosstabs have a Column Headings property that you can type in all possible column heading values.

Duane
Hook'D on Access
MS Access MVP

 

[MeldrethMan]

Great, thanks, works a treat.