Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    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?
    Charlotte

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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>
    Attached Files Attached Files

  9. #9
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.
    Attached Files Attached Files

  12. #12
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.)

    I made a slight modification to the PageHeaderSection:

    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. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #14
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.
    Attached Images Attached Images
    • File Type: png x.PNG (3.6 KB, 0 views)

Page 1 of 2 12 LastLast

Posting Permissions

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