Displaying Multiple Listings in Columns 2

[awl]

I am looking for a way to display 2 fields of data that have multiple listings in “columns” in a report. Example: I want to send in a letter to my clients a listing of 320 destinations that come from 2 fields; [Destination] and [Category]. Normally the first 40 or so names appear on the first page, then the next page will have 80 names, and the 3rd page another 80 names, and so on until the full 320 names have been listed. Instead, I would like to display the names alphabetically in columns of 3 on the first page, then the next page will continue listing the names in columns of 3, until the 320 list is fully displayed. Instead of 4 or more pages, the letter will consist of only 2 or 3 pages. A more visual example is when Bank of America sends out their client’s bank statements: the statement lists the “Checks Posted in Numerical Order” which are displayed in columns of 3 all on one page. The check numbers are in sequential order as in example below.

Check # Posted Amt Check # Posted Amt Check # Posted Amt
7210 5-16 44.18 7236 5-12 52.50 7258 6-6 19.90
7211 5-16 68.84 7242 5-13 8.00 7259 6-7 9.50
7212 5-20 13.83 7243 5-20 252.50 7262 6-7 33.58

Any suggestions would be greatly appreciated. Thanks…

 

[Remou]

Hi
Have you looked at Columns on Page Setup?

 

[Trevil]

Using your example above, create a report with the three fields (Check, Posted & Amt). Then click on File|Page Setup then click on the 'Columns' tab and enter 3, then check 'Down then across'

Code: Where the vision is often rudely introduced to reality!

 

[awl]

Yes, thank you Remou and Trevil for your response. I followed your suggestions and was able to get my 3 fields into 3 sets of columns on my report. A question: Is there a way to limit the size of the Detail in how many records are displayed in each column, that is to have each set of columns with the equal number of records as displayed in the Bank Statements? Currently with the Column Layout as “Down, then Across”, my first set of column data display records all the way to the bottom of the page, then the 2nd column displays the remaining amount of data, leaving the rest of the column space blank, the 3rd column is totally blank, and finally, the 2 controls that I have in my Report Footer, is forced off to the 2nd page all by itself. No matter how many records there are, the 2 controls in the Report Footer are always displayed on a separate page. How can I evenly space the 3 sets of columns on the 1st page and get the 2 controls of the Report Footer on the same 1st page?. If I change the Column Layout in the Page Setup to “Across, then Down”, the 2 controls in my “Report Footer” then are displayed on the 1st page as I want, however, the detail data is now displayed across the page, which is what I don’t want. Thank you.

 

[awl]

In addition to the above, I looked at the design of the Northwind Samples database: Products by Category report, and see that they have only a CategoryName Footer, not a Report Footer.

 

[Trevil]

Here's a solution I found on another site, but you may not like it:

http://www.utteraccess.com/forums/s...&page=0&view=collapsed&sb=9&o=&fpart=all&vc=1

The only other way I can think of would be to write code that checks the recordcount on a page then adjusts the size of the footer to take up more space. This should force the columns to even up, but could be a nightmare with multiple pages.

Or (light just came on) you could look at how it was done in NorthWind (Products by Category ), and try a variation of that. The reason NorthWind's report is even is because of the Grouping on each column. All (hah!) you would need to do would be to have your query assign a dummy field that contains the same value 1/3 of the records, another for the second 1/3 etc. Then your report could Group on that field -- just don't print it. You may need to create a temp-table where you could manipulate the 'group' name, then use that as input to your report.

Code: Where the vision is often rudely introduced to reality!

 

[Remou]

Hi
Apparently the trick is to use a group footer, rather than a report footer. It involves creating a group that occurs once for the entire report. See this

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart01/html/sa01e10.asp

Regarding balancing columns evenly; I think something similar might be done with code. For example, divide each group by three and assign a number to each set; use these numbers for grouping; set New Column to After Section. I have never tried it and I dare say it is not what you were thinking of.