Single/Multi Column Report

[PPCO]

I have some daily production data that I would like to make into a
report--each job number/production data has the names of the employees who
worked on it for that day. Some of the jobs have multiple employees who
worked on it for that day, and so on the report it will show up on 4 lines if
it had 4 employees. Is there a way to put the employees all together under
one line/job? Do I need to do a sub report and link the employee names to the
job number?

 

[Ken Sheridan]

A subreport with four 'across then down' columns is by far the easiest
solution, but it can be done in a single report by manipulating the position
of the controls at runtime. Here's the module for a report I once produced
to illustrate this. The report lists the occupants of each address across a
single line, wrapping to the next line after every fourth occupant. Exactly
he same result was produced much more simply by means of a subreport, however:

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 fourth 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



Ken Sheridan
Stafford, England

 

[PPCO]

So if there were more than 4 employees, than I would need more columns? Thanks!

 

[Ken Sheridan]

Not necessarily; it would start a new line with the fifth employee under the
first in column 1. This assumes that the subreport is set up with four
columns of course, or that doing it in code with a single report the
COLUMN_COUNT constant is given a value of 4. In either case you can have
fewer or more columns, though the page width will set a practical limit of
course.

Ken Sheridan
Stafford, England

 

[PPCO]

So how do I make sure the records line up next to each other between the
report and subreport? Or do the Employee names in the subreport have to be
underneath the production record? Thaks

 

[Ken Sheridan]

The subreport can go wherever you like in relation to the corresponding row
of the main report. If you want the employees listed on the same line as the
main report's detail then put the subreport control in the right part of the
detail section immediately to the right of the main report's production
control(s). If you have four columns in the subreport and more than four
employees the fifth employee will be on a new line immediately below employee
1.

The code solution I posted also puts them there BTW.

If you want to see this in action mail me at:

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

and I'll send you the .mdb file containing the code and subreport solutions.
It was originally produced for a magazine column written by a contact of
mine, but I doubt its still available on their web site.

Ken Sheridan
Stafford, England

 

[PPCO]

Thanks! I'll e-mail you...

The subreport can go wherever you like in relation to the corresponding row
of the main report. If you want the employees listed on the same line as the
main report's detail then put the subreport control in the right part of the
detail section immediately to the right of the main report's production
control(s). If you have four columns in the subreport and more than four
employees the fifth employee will be on a new line immediately below employee
1.

The code solution I posted also puts them there BTW.

If you want to see this in action mail me at:

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

and I'll send you the .mdb file containing the code and subreport solutions.
It was originally produced for a magazine column written by a contact of
mine, but I doubt its still available on their web site.

Ken Sheridan
Stafford, England