Solved Trying to figure out how to start new column in a report after a given amount of records (1 Viewer)

eibyer_apc

New member
Local time
Yesterday, 16:22
Joined
Mar 1, 2022
Messages
14
I have a query that generates records from 100 to 140. I'd like to distribute the printout into 3 columns. I got as far as getting my result count into a variable but am not sure how to start the new column when my desired number of records for the first column is reached.

I'm more comfortable in Excel, just getting my feet wet in Access :)

Thank you!
 

GPGeorge

Grover Park George
Local time
Yesterday, 16:22
Joined
Nov 25, 2004
Messages
690
I have a query that generates records from 100 to 140. I'd like to distribute the printout into 3 columns. I got as far as getting my result count into a variable but am not sure how to start the new column when my desired number of records for the first column is reached.

I'm more comfortable in Excel, just getting my feet wet in Access :)

Thank you!
I think there's more to the question that appears on the surface.

First, for example, the title of the post, is "Trying to figure out how to start new column in form...." Forms are on-screen only, and not intended to be printed out, even though it's possible to do so. Reports are, on the other hand, designed to be printed out, but can be displayed on-screen. So it's important to know what exactly the problem is. Are you actually trying to print out a form? Or are you talking about creating a report to print?

Second, columns are one of the features of reports, but not of forms. So it's a moot point if, in fact, you really do mean forms.

That said, you could embed a report with columns as a subform in a subform control in a form, if that would meet the requirement.

So, can we clarify what's going on? Thanks.
 

eibyer_apc

New member
Local time
Yesterday, 16:22
Joined
Mar 1, 2022
Messages
14
My bad, you got me, see I told you I was a newbie. I meant report, not form! Let me see if I can edit the thread title.

Edit: Also, I believe this thread should be moved to the appropriate section also.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:22
Joined
May 21, 2018
Messages
6,371

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 16:22
Joined
Oct 29, 2018
Messages
19,002
Edit: Also, I believe this thread should be moved to the appropriate section also.
Hi. Welcome to AWF!

As requested, I moved your thread to the Reports forum. Good luck!
 

SHANEMAC51

Active member
Local time
Today, 02:22
Joined
Jan 28, 2022
Messages
288
I'd like to distribute the printout into 3 columns.
I would like to decompose the printout into 3 columns.
1 - if the elements are placed horizontally, a standard report is sufficient, without code (Fig. 1)
2 - if vertically, then you will have to write program code (Fig. 2)
 

Attachments

  • Screenshot_61.png
    Screenshot_61.png
    11.5 KB · Views: 98
  • Screenshot_6.png
    Screenshot_6.png
    17.8 KB · Views: 93

eibyer_apc

New member
Local time
Yesterday, 16:22
Joined
Mar 1, 2022
Messages
14
I would like to decompose the printout into 3 columns.
1 - if the elements are placed horizontally, a standard report is sufficient, without code (Fig. 1)
2 - if vertically, then you will have to write program code (Fig. 2)
@SHANEMAC51, figure 2 is what I'm after so I'm definitely going to have to code something together.
 

SHANEMAC51

Active member
Local time
Today, 02:22
Joined
Jan 28, 2022
Messages
288
figure 2 is what I'm after so I'm definitely going to have to code something together.
lay out an example of a database with a report in 1 column , I need to understand if it differs from mine
3-4 record
 

eibyer_apc

New member
Local time
Yesterday, 16:22
Joined
Mar 1, 2022
Messages
14
I am not sure if we're allowed to upload a sample db (I didn't search yet) so I just clipped it.

This table may grow up to about 160ish in record count. What I'm trying to accomplish is to divide it into 3 columns. In this example, the query would have 19 records so I'll end up with 2 columns of 6 records each and the third column with 7.

Am I looking for some kind of event that checks what record number was last entered in the report so I can start the next column if that record was number 6. I hope that made sense. Thanks for looking!


1646155874409.png
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:22
Joined
May 21, 2018
Messages
6,371
That said, you could embed a report with columns as a subform in a subform control in a form, if that would meet the requirement.
As said the vertical solution will require code. Horizontal does not.
That is one way to do it. Maybe there is an easier way. If you had three subforms to look like columns then.
1. Determine number of records
2. Divide by 3 to get N and get remainder R
3. Update three query definitions (Pseudo code)
Q1: Select Top N ContactName from table1Order By ContactName
Q2: Select Top N ContactName from Table1 where ID Not in (Select Top N ID from Table1 Order By ContactName) Order by ContactName
Q3: Select Top (N+R) ContactName from table1 Where ID Not In (Select top (2N) ID from Table1 Order By ContactName) Order By ContactName
4. Set the source objects of the three subforms.

The Other way is a TempTable. For me this is a lot easier
The Temp table has three Columns. C1, C2, C3
1. Determine number of records
2. Divide by 3 to get N
3. Write to the TempTable. After each N record start on the next column. For the last column put all remaining records

This works fine until you exceed what can go on one sheet.
 

eibyer_apc

New member
Local time
Yesterday, 16:22
Joined
Mar 1, 2022
Messages
14
Thank you for this, let me see what I can put together using your suggestion.
 

SHANEMAC51

Active member
Local time
Today, 02:22
Joined
Jan 28, 2022
Messages
288
I have a query that generates records from 100 to 140
using an example of 137 rows, adding a service field to the table for grouping, taking into account the possible extension = yes, I got an option with setting the number of records in the column
 

Attachments

  • Screenshot_7.png
    Screenshot_7.png
    33.1 KB · Views: 89

SHANEMAC51

Active member
Local time
Today, 02:22
Joined
Jan 28, 2022
Messages
288
I have a query that generates records from 100 to 140
137 entries in font 8 fits into the page,

it is necessary to somehow still try students by classes in 3 columns, although this can apparently be implemented without an additional field, since there are clearly less than 100 people in the class (obviously it will fit into the page)
 

Attachments

  • Screenshot_8.png
    Screenshot_8.png
    42.2 KB · Views: 87

SHANEMAC51

Active member
Local time
Today, 02:22
Joined
Jan 28, 2022
Messages
288
although this can apparently be implemented without an additional field, since there are clearly less than 100 people in the class (obviously it will fit into the page)
 

Attachments

  • Screenshot_9.png
    Screenshot_9.png
    56.8 KB · Views: 92

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:22
Joined
May 7, 2009
Messages
16,453
simple division by 3 the total records
and using temporary table.
p2.png
p3.png
 

Attachments

  • Report3Column.accdb
    772 KB · Views: 91
  • p1.png
    p1.png
    34.3 KB · Views: 90

SHANEMAC51

Active member
Local time
Today, 02:22
Joined
Jan 28, 2022
Messages
288
simple division by 3 the total records
and using temporary table.
I think that the records should be sorted
sheet 1 - 150 minimum, 3 columns from top to bottom
sheet 2 - the next 150
last sheet - the remainder, for example 3 columns of 10-11 values

moreover, the values can be longer than 10-12 characters, 150 last name + first name may not fit on the sheet
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:22
Joined
May 7, 2009
Messages
16,453
this is sorted.
what more can you ask?
 

Attachments

  • Report3Column.zip
    113.7 KB · Views: 89

eibyer_apc

New member
Local time
Yesterday, 16:22
Joined
Mar 1, 2022
Messages
14
Woah, lots of activity to sort out. I appreciate it guys/gals, this will take me a bit to digest. Thank you!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 07:22
Joined
May 7, 2009
Messages
16,453
this is report of sample names.
 

Attachments

  • Report3Column.zip
    99.5 KB · Views: 92

Users who are viewing this thread

Top Bottom