Report Laterally

[cooke.christopher]

I have three tables that this report concerns:

1. JudgeData
2. NomineeData
3. Assignments


The Assignments table is the focus of the report. Each entry contains
one JudgeID and one NomineeID, but no judge has the same nominee
twice, but each nominee has multiple judges.

I am creating a report that mimics a spreadsheet. Each line shows the
NomineeID and related information pulled from the NomineeData table.
The complex part is to have each of the related judges displayed
laterally, not vertically as is way a standard details section/
subreport would work. I want this to be expandable, but in reality no
nominee has more than 5 judges. The "spreadsheet" fields would look
like:

NomineeID NomName Affiliation Judge1 Judge2 Judge3 Judge4
Judge5

I have tried to create a query (crosstab and select) that will
assemble the data appropriately, and that has failed. I can easily
crate the report using sections/subreports but of course the data is
not displayed how i would like.

My ideas are:

-have the sub report populate left to right (how?)
-Have a query build the data correctly and then make a simple report.

Any ideas?

 

[Tom Wickerath]

Hi Christopher,

I have tried to create a query (crosstab and select) that will
assemble the data appropriately, and that has failed.

What happened or did not happen when you used a crosstab query?

Does this tutorial help at all?

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Ken Sheridan]

You can do this very easily by means of a multi-column subreport. Position
the subreport to the right of the nominee and affiliation controls. When
designing the subreport select Page Setup from the File menu, and then select
the Columns tab of the dialogue. Set the width of the report to the
available space on the part of the page occupied by the subreport. Place a
text box control bound to the judge field in the far left of the detail
section. Uncheck the 'Same as Detail' check box for the column width. Enter
values for the number of columns and column width so they fit in the
available space. Set the column layout to 'Across then Down'.

When the report opens the judges will be shown horizontally across the right
part of the page. If there are more judges for a nominee than will fit in
the available columns the next one will be positioned on the following line
immediately below the first judge, then the next below the second and so on.

Out of interest the same effect can also be achieved with a single report
based on a query joining the tables. In this case the layout is manipulated
in code at runtime. I once did this for a contact of mine who writes a
databases column in a magazine in response to an enquiry by one of his
readers, just to show that it could be done rather than as a practical
solution. It brings to mind Samuel Johnson's comment about a dog walking on
its hind legs in that 'the wonder is that it is done at all, not that it is
done well'. The report in question was for multiple occupants at single
addresses in a club membership database, and the code for its module was:

Option Compare Database
Option Explicit

Const FP_ORIGIN = 3315 'original position of FP control
Const FN_ORIGIN = 3576 'original position of FirstName control
Const OFFSET = 1440 'distance controls to be moved to the
'right (one inch in Twips )
Const COLUMN_COUNT = 4 ' number of columns for occupants

' declare module level variables
Dim lngAddressID As Long
Dim n As Integer, i As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim intOccupants As Integer

' get number of occupants at current address
intOccupants = DCount("*", "People", "AddressID = " & Me.AddressID)
' increment count of occupants in module level variable
i = i + 1

' if first occupant print FP and FirstName at start postion
If n = 0 Then
Me.FP.Left = FP_ORIGIN
Me.FirstName.Left = FN_ORIGIN
' move print location to next line if only one occupant
MoveLayout = (i = intOccupants)
Else
' move FP and FirstName controls one inch to right
Me.FP.Left = FP_ORIGIN + (OFFSET * n)
Me.FirstName.Left = FN_ORIGIN + (OFFSET * n)
' if this is not last occupant for current address then
' don't move print location to next line
If AddressID = lngAddressID And i < intOccupants Then
MoveLayout = False
End If
' if this is third occupant printed on this line then
' move print location to next line
If i Mod COLUMN_COUNT = 0 Then
n = -1
MoveLayout = True
End If
End If

' increment count of occupants printed on current line
' in module level variable. NB count is zero-based
n = n + 1
' assign current AddressID to module level variable
lngAddressID = Me.AddressID

End Sub

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)

' don't move print location to next line so that
' first detail (occupant) prints on same line as
' group header
MoveLayout = False
' intialize module level variables to zero for start of
' occupants list for new address
n = 0
i = 0

End Sub

If you have any difficulty implementing this I can send you the original
files, one with a subreport, one using code, which were published on the
magazine's web site to accompany the article. Just mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

Ken Sheridan
Stafford, England

 

[cooke.christopher]

Ken, thanks a bunch your solution was perfectly simple. I started
solving my problem in VBA (much like your suggestion) last night
before I went home but luckily you had posted and prevented me from
going too far.

Christopher