Displaying Data as a Pyramid

[Andy Williams]

I have a Sports Club database.

The club runs a Pyramid Challenge System whereby members are ranked and then
arranged in a Pyramid (highest ranking at the top).

Members can challenge the person immediately above them to a match and if
they win the Members exchange places. New members start out off the pyramid
and can challenge anyone on the bottom row of the pyramid to a match and if
they win they move onto the pyramid (and the loser moves off).

I would like to be able to display the current pyramid on an Access Form and
maybe an Access Report but can't get my head round how to do it.

Any help would be greatly appreciated.

Thanks

Andy W

 

[Martin J]

Put a level field as integer in your data. New members start at 0. When they
get on the pyramid they become 1. Your highest level would be the greatest #.
To display it you'd sort by level >0 descending. If you want it to look nice
go to Duane Hookum's Website s he has code to make records that appear 1 to a
line appear like a comma delimited list.

 

[Duane Hookom]

I had given some thought to the generic concatenate function also to combine
the names from a single level. The code can be found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

 

[Andy Williams]

Thanks to both Martin and Duane for the responses but I'm afraid I can't see
how they help me.

I've done an HTML version of what I want the form / report to look like
which can be viewed here. The names on the right are the New Members (i.e.
those that are trying to get onto the pyramid).

http://www.bandtogether.co.uk/Testing/TestPyramid.html

If I've missed something in your original replies please let me know.

Thanks

Andy W

 

[Duane Hookom]

Do you always have 1 at the top, 2 on the next level down, then 3, 4, ...?
Is it important to display the new members on the right?
How many levels max would you expect and what would you do when you have too
many names to fit across the page?
What is your table structure so we know how you are defining levels?

 

[Andy Williams]

Duane

Each person in the Pyramid has a ranking with the person at the top of the
Pyramid as Rank 1 and the person on the right hand end of row 5 having Rank
15.

The Pyramid will be 5 Rows high (unless we end up with less than 15 members
but if that happens we probably won't have a club!) and will always be
constructed by rank as follows:-

1
2 3
4 5 6
etc

I was planning on having a table which stored the Rank and the MemberID

Rank MemberID
1 2
2 6
3 7
4 3

etc

Only members on the Pyramid will feature in this table so anyone not in this
table would automatically appear in the right hand list.

Hope this helps you to help me.

Thanks

Andy W

 

[Martin J]

This is a simple way of doing it. Make a report with 15 labels in a pyramid
shape. The label names should be label1 or l1(just cutting down on typing).
In report open event have the following code

Private Sub Report_Open(Cancel As Integer)
Dim a As Integer, rsttest As DAO.Recordset
Set dbsmed1 = CurrentDb()
Set rsttest = dbsmed1.OpenRecordset("test3", dbOpenDynaset)
With rsttest
Do While Not .EOF
Me("l" & ![rank]).Caption = ![member id] ' can be name if joined
..MoveNext
Loop
End With
End Sub

The test3 recordset should be the following query...
SELECT test2.[member id], test2.rank
FROM test2
GROUP BY test2.[member id], test2.rank
HAVING (((test2.rank)>0))
ORDER BY test2.rank DESC;

Test2 is your table with ranks and member id.

Then make a sub report for the rest of your members.

 

[Duane Hookom]

First create a small function that calculates which level of the pyramid
where 1 is the top and 2 is next:
Function GetLevel(pintRank As Integer) As Integer
Select Case pintRank
Case 1
GetLevel = 1
Case 2 To 3
GetLevel = 2
Case 4 To 6
GetLevel = 3
Case 7 To 10
GetLevel = 4
Case 11 To 15
GetLevel = 5
Case 16 To 21
GetLevel = 6
Case 22 To 28
GetLevel = 7
Case 29 To 36
GetLevel = 8
End Select
End Function

Use this function in your report's recordsource. I used the employee table
from northwind:

SELECT LastName, FirstName, EmployeeID AS Rank,
GetLevel([EmployeeID]) AS [Level]
FROM Employees;

Then create your report an group first by Level (with a Group Header) and
then by Rank (no header). Make sure the Level Group Header and the Detail
section are both the same height (I used .25"). Make the report as wide as
possible for your printer and paper size. Maybe use landscape.

Add a text box to the Group Header:
Name: txtNumInLevel
Control Source: =Count(Rank)
Visible: No

Add text boxes to the detail section:
Name: txtCount
Control Source: =1
Running Sum: Over Group
Visible: No

Name: txtName
Control Source: =[FirstName] & " " & [LastName]
Visible: Yes
Back Style: Normal
Text Align: Center

Add code to the On Format event to the Detail Section:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim intNumPlayers As Integer
Dim intReportWidth As Integer
Dim intNameWidth As Integer
Dim intLeft As Integer
Dim intcount As Integer
Dim lngBackColor As Long
Dim intHue As Integer


Const intMaxWidthPlayer As Integer = 1600
intNumPlayers = Me.txtNumInLevel
intReportWidth = Me.Width
If (intNumPlayers * intMaxWidthPlayer) > _
intReportWidth Then
intNameWidth = intReportWidth / intNumPlayers
Else
intNameWidth = intMaxWidthPlayer
End If
Me.txtName.Width = intNameWidth
intcount = Me.txtCount
intLeft = (intReportWidth / 2) - _
(intNumPlayers / 2) * intNameWidth _
+ intNameWidth * (intcount - 1)
Me.txtName.Left = intLeft
' remove next 3 lines if you don't want pretty
intHue = 255 - (intNumPlayers - 1) * 10
lngBackColor = RGB(intHue, intHue, intHue)
Me.txtName.BackColor = lngBackColor
Me.MoveLayout = False

End Sub