# Thread: Columns In Access2000 Report (9.0.3821 SR-1)

1. ## Columns In Access2000 Report (9.0.3821 SR-1)

I have a two-part problem but I'll just start with the first part first. I need to duplicate a report at work that is currently being generated manually. The report has six columns and must contain an equal number of records (names) in each column. (If the amount of records divided by 6 does not equal an even amount, then column 6 will have the fewer names.) I have created the six columns but I don't really have a clue as to how to write the code required to do the math in order to format each column. (Part two of my problem takes each column and generates a separate report/page.) I certainly appreciate your help!

2. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

Select File | Page Setup. In the Columns tab, select 'Across, then Down'. I think this will do what you want, Access will take care of the maths for you.

Can you explain in more detail what you want to accomplish in part two?

3. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

Thanks for the reply, Hans. However, I had already created the columns. What I am trying to do is put the same number of records in each column equally across the six columns. For example, if the there are 90 records in the underlying query, then there should be 15 names in each column. But if there are only 72 records in the underlying query, there has to be 12 names in each column. If there are 71 records, then there should be 12 names in the first five and 11 in the sixth. Hopefully, this presents a more clear picture of my problem. Thanks again for your help.

Sincerely,
Jon

4. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

That's exactly what Across then Down will do, so what is the problem?

5. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

Yep, you're right and I'm a dumbass! I forgot to mention I need the columns in alphabetical order starting in column one down and then over to column two down and so on. Sorry for not being complete to begin with.
Sincerely,
Jon

6. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

You don't believe in making things easy, do you? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

I'm not sure if I can come up with a solution, but I'll look into it (if anyone else already solved this, please post!)

7. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

So what you really want to do is divide the total number of names by 3 and restrict the number of rows to make the columns the same length, right?

8. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

This was harder than I initially thought, and my solution is probably more complicated than necessary. I have attached a zipped Access 97 database with one table (Customers, from the Access 97 Northwind database) and one report (rptColumnTest).

You need the following controls:
<UL><LI>There must be a text box that refers to the number of pages. This forces Access to calculate the number of pages immediately, instead of after the fact. The text box may be hidden if you like.
<LI>I used an invisible text box in the detail section with Control Source set to =1, and Running Sum set to Over All. This text box (txtCounter) is used to keep track of the record number.[/list]This is the code behind the report:

<img src=/w3timages/blueline.gif width=33% height=2>

' Declarations

Private Const intColCount = 6

Private intTotalRecs As Integer
Private intRowCount As Integer
Private intRowsPerCol As Integer
Private sngTop As Single
Private sngOldTop As Single

' On Format handler of detail section - this is where it all happens

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Check if we're on the last page
If Me.Page = Me.Pages Then
' The Top property tells us if we're at the top of a column
sngOldTop = sngTop
sngTop = Me.Top
' Have enough records been shown in this column?
If intRowCount = intRowsPerCol + 1 Then
Me.NextRecord = False
Me.CompanyName.Visible = False
ElseIf intRowCount > intRowsPerCol And Me.txtCounter <= intTotalRecs Then
If sngTop > sngOldTop Then
Me.NextRecord = False
Else
Me.CompanyName.Visible = True
intRowCount = 1
End If
End If
End If
End Sub

' On Print handler of detail section - increase row counter

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Me.PrintCount = 1 Then
intRowCount = intRowCount + 1
End If
End Sub

' On Format handler of page header - check if we're on last page
' Initialize variables needed on last page

Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
Dim intRemaining As Integer
If Me.Page = Me.Pages Then
intRowCount = 0
intTotalRecs = DCount("*", "Customers")
intRemaining = intTotalRecs - Me.txtCounter
intRowsPerCol = (intRemaining + intColCount - 1) intColCount
End If
End Sub

<img src=/w3timages/blueline.gif width=33% height=2>

9. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

No - divide the total number of names by six and then populate the columns equally. If there are an odd number of names, then column 6 will have the fewer names. But yes, in a sense I am trying to make the rows even as well with the last row shorter if there are an odd number of names.

Jon

10. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

Hans,
I have added your solution to my report but I'm not seeing that it is doing anything. I don't see where it is trying to divide the number of records in the underlying query by 6. Am I missreading it? In any case, I have attached the applicable sections of what I am trying to do so you can more accurately see what's going on.
Sincerely,
Jon

11. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

There are several problems:
<UL><LI>I wrote "There must be a text box that refers to the number of pages." You didn't put one in the page footer.
<LI>In my example, the page header was named PageHeader, in yours PageHeaderSection. You copied the code without taking this into account.
<LI>You refer to a non-existing query PRTDivisionRowSheet in the code, that should be qryPRTDivisionRowSheet.[/list]See attached revised database.

12. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

I tell my recruits that failure to pay attention to detail will get people killed - and then I go and commit the unpardonable sin myself!

We are very close to where I need to be. (Let me throw another wrinkle at you - the people in columns 1 & 2 are partners, 3 & 4 are partners, and 5 & 6 are partners. That means I have to get columns 5 & 6 as equal as possible.)

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
Dim intRemaining As Integer
If Me.Page = Me.Pages Then
intRowCount = 0
intTotalRecs = DCount("*", "qryPRTDivisionRowSheet")
'intRemaining = intTotalRecs - Me.txtCounter
'intRowsPerCol = (intRemaining + intColCount - 1) intColCount
intRowsPerCol = (intTotalRecs intColCount)
End If
End Sub

This seems to work great except when the total number of records is not a multiple of six. Once I can get 5 & 6 as equal as possible, we can move on to part two - which is to populate a six page report based on the 6 columns of this report. (I've never had to do anything as complicated as this before.)

I really appreciate the time you're spending with me on this!
Thank you,
Jon

13. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

Let's try to get the problem straight before we continue.

First, you asked for the six columns to be divided as evenly as possible.
I suggested printing across, then down.
You then added that the records had to be printed down, then across. You stll required that the six columns had to be distributed evenly.
I posted a solution that took care of that.
Now, it turns out that the columns are to be paired. It is not clear to me at all how this is to be related to the data.
You also mention that you're going to have to create a six-page report.

I fear that we could go on this way for a long time - each time I propose a solution, you come up with a new condition you hadn't mentioned before and that invalidates the solution to the previous question. So could you please state precisely and in detail what you want to accomplish in the end, with all restrictions and conditions, if possible with an illustration of the desired end result.

14. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

Hans,
First, let me apologize for frustrating you in this whole saga. That was certainly not my intention. I am sorry. And I do appreciate your willingness to not tell me to just go away.

Second, I am a Division Commander at the U.S. Navy boot camp and therefore am working about 18-20 hours a day training new recruits. That means I can only work
on this project during the 4-6 hours I am able to be at home - when I should be sleeping. Perhaps I should have been sleeping since I obviously haven't been thinking clearly or logically.

The function of this report is a roster for a physical fitness test the recruits have to take. Due to limitations in the facilites, the recruits are lined up in six rows. Rows 1 & 2 face each other, 3 & 4 face each other, and 5 & 6 face each other. (A division can have any number of recruits in it - usually around 65 to 90.) [Definition: Row = Column in Access]

If there are 90 recruits, then there will be 15 recruits in each of the six rows paired off evenly. This setup allows one recruit to do the exercises while the other partner counts how many exercises are done. Because of this arrangement, there should only be one recruit in row 5 without a partner in row six if there is an odd number of recruits (not divisible by 6) in the division. In this situation, the PT staff will assign a straggler from another division to the odd man out as a partner in order to fill row 6. If row 6 is two recruits short from row 5, then one recruit from five is moved over to row 6 - evening out 5 & 6. Note that now both rows 5 and 6 will be one short of the first 4 rows.

The PT staff require each row be in alphabetical order starting with row 1. When I talked to them, they were adamant about this - I couldn't change their minds to make it easier.

Right now this roster is being hand printed! That's a lot of writing when there are several thousand recruits here at any one time.

I probably should not have mentioned the six page report yet but since I did: Each page just shows each row from this report we're working on right now. Page one will have the records (recruits) from row 1, page two will have the records from row 2, and so on. I'll cross that bridge when we come to it.

Hopefully, I have shed a better light on what I am doing and trying to do. I am very thankful for the help you have already given me and you have gotten me closer to the goal in spite of my feeble attempts to relay exactly what I wanted.

Sincerly,
Jon

15. ## Re: Columns In Access2000 Report (9.0.3821 SR-1)

If the number of recruits is divisible by 6, there is only one solution.
If the remainder after division by 6 is 4 or 5, it's easy two - the most desirable distribution is obvious,
But if the remainder after division by 6 is 1, 2 or 3, there is more than one possible distribution. I have illustrated this with little charts for 31, 32 and 33 recruits. Can you indicate for each case which distribution (a, b or c) you prefer? Also, if you would like the columns (rows as you call them) to be in a different order than in the charts, please specify.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•