Setting up a report across a page rather than down 2

[jedel]

Hi,

While I've been building databases for a little while, I do not spend near enough time on the report side of the house. Most of my output goes to word documents.

In my latest project however, I found the need to go to a report, but I need the records to read across the page. Let me explain....

The project is a rostering project for a church band and all of the singer, musicians and production staff for a service is list down on column on a lanscape page with the "header" on the left hand side of the page. The data is a subreport with eight columns, displaying eight events on the first page. All works fine until I get to the second page where there are no headings.

How can I get the headings to flow to every subsequent page after the first. ALternatively, if there is any other way of displaying the data in columns across a landscape page, I'm all ears...

Thanks

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[genomon]

Try setting the Header RepeatSection property to yes.

Knowledge is knowing a tomato is a fruit; Wisdom is not putting it in a fruit salad.

 

[jedel]

How does this make a difference when the "Headings" are down the left hand side of the page in the body section?

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[dhookom]

jedel,
Are you using the solution from the MS KB article on displaying labels only on the left for a multicolumn report?

Can you provide more information about your record sources and how you have your reports set up?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[ck1999]

Since you are familiar with exporting to word can you export to excel and fix the page setup where to always print column A.

Might be a work around

Are your "headings" in the detail section of your report since from my understanding they are to the left of the data

ck1999

 

[jedel]

Thanks guys for your responses.

ck1999, Could you provide me with an example or someplace to look at doing what you said into an excel speadsheet? This is where the roster originally came from so I could modify the original document to suit.

dhookum,
No, I'm not, At the moment I have a master report and a subreport. Both forms source the same table and are connected by a month field. The master report has the headings and the subreport carries the data in eight columns per page. This was the only way I could think about setting the data up.

I would very much like to read the article you mentioned. Do you have a location for it?

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[ck1999]

This is what I am thinking
Column A has all the "Headings" then col b - Col ? have your data.

go to file>page setup click sheet tab there is an option for columns to repeat at left.

ck1999

 

[dhookom]

Here is the KB Article

http://support.microsoft.com/kb/208491/en-us.

Your actual table structure and SQL isn't clear. There may be an alternative solution.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[jedel]

ck1999
I understand the layout. What I don'tg know is how to transfer all of the data to the spreadsheet using VBA.

dhookum
The article looks interesting, I got a little lost when it said place the labels over the top of the text box controls, but I'll try it out and see what happens.

With regards to my table structure and SQL.

The table name is tblSample, it has a unique Key field which is an autonumber. Then is has about 6 fields that describe the event and about 20 fields that have names for the roster. Pretty straight forward table

The SQL for the master Report is a simple SQL showing all records where the month field matches the selected month from a combo box in a form. This way I can print a one month roster that is in date order and then in time order.

I hope this cleared it up for you.

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[ck1999]

Public sub ExportQuery() As String
On Error GoTo err_Handler

    'Excel object variables
    Dim appExcel As Excel.Application
    Dim wbk As Excel.Workbook
    Dim wks As Excel.Worksheet
    
    Dim sTemplate As String
    Dim sTempFile As String
    Dim sOutput As String
    dim counter
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim sSQL As String
    Dim IRecords As Long
    Dim iRow As Integer
    Dim iCol As Integer
    Dim iFld As Integer
     
   sOutput = CurrentProject.Path & "\Test.xls"
    
    'Create the Excel Application, Workbook and Worksheet and Database object
    Set appExcel = New Excel.Application
    appExcel.Visible = True
    Set wbk = appExcel.Workbooks.Open(sOutput)
    
    sSQL= "SELECT * FROM tblSample"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)
    If Not rst.BOF Then
    rst.Movefirst
counter = 1
    Do While Not rst.EOF
        With wbk
            .Sheets("sheet1").cells(counter,"A") = rst.Fields("HeaderName")
            .Sheets("sheet2").cells(counter,"B") = rst.Fields("Name1")

' repeat these statements for each field
            counter = counter +1

        End With
        rst.MoveNext
    Loop
    rst.Close
    
   
exit_Here:
'Cleanup all objects (resume next on errors)
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
Exit Function

err_Handler:
    ExportQuery = Err.Description
    Resume exit_Here
 End If
End sub

this was modified from thread 705-1420958

if this does not work exactly you may want to start a new threaed in forum access modules (vba coding) and post what you have so far. I am not sure of what your field names are.

ck1999

 

[dhookom]

If you want to keep this all in Access, I would probably normalize your table structure and then build a crosstab report. I expect your "20 fields that have names for the roster" represents an un-normalized structure. I could be wrong.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[jedel]

Guys,

Thanks both for you posts. They both have good merit. CK, I will be saving your code for another time. I've always wanted to know how to get multiple records into another office document.
dhookum,
I think the crosstab report was a little too complex for this report.
The article you sent me was just what the doctor ordered. I gave it a try on the Northwind database and it worked nicely. I'm going to give it a whirl on my project, possibly with a little twist. I'll post my solution when I'm done.

Cheers

Dean

-------------------------------------------------------------
"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."