Wrap text, multiple columns, and misc reporting issues

[georgedwilkinson]

This is my first post to these forums, please be gentle with me. Also, I know very little about Access reporting, having avoided the topic for all these years.

My company wants to mail a listing of work sites, by state (i.e. geographic region), to all of our living clients. This is important because most of our clients will be dead within a year and it is time sensitive information. Their perusal of the list could result in additional income for them and/or their heirs at a time when they need it most.

Our site list is approximately 125K records. Additionally, the "man" (aka the president of my company) wants to send the list on paper, since many of our clients will be unable to get out of bed to go to a computer. Additionally, the "man" wants to segregate the report by state, only sending the list for the states a client has been to. Also, realize the clients are old and may have vision problems or may have problems holding reams of paper for long periods of time.

I had recommended that we send our data set out to a printer and have them do the work. I think that was kicked out because of the confidential nature of our data set.

I have been experimenting with Access' built in reporting and have come across the following issues:

1. Performance...although the underlying query returns quickly within SQL Analyser/Access, it takes forever to generate a report on our largest state (California). During that time, my PC slows to a crawl.
2. Field wrapping...Some of our site names are quite long, added to the fact that I'm concatenating several fields to comprise the name. As such, the names run off the edge of the page/column, causing lost data when using Access reporting. This is the problem that Excel solves by using the "Wrap Text" formatting property. I'd like to use something like that from within Access.
3. Number of Columns...(not database fields, columns on a page) when using the Access tools, seems you can only have either 1 or 2 columns on a page and that the layout of the page is inflexible (or, more likely, escapes my ability to understand it). To save paper and reduce the weight of the paper being held by our clients, I have decided to split the report into 2 columns...though Access reporting seems to kind of do this, it doesn't seem repeatable/customizable. I need something like Publisher's links, in conjunction with Excel's wrap text to make this work right. If it weren't for the column limitation, I would just use Excel and be done with it. Is there any way to configue Access reporting (or even Excel) to do this?
4. Cell shading...I'd like to shade every other entry, like the cell shading in Excel, to help the client understand which row they're on. Any way to do this in Access reports?

Any advice anyone can give me on flexible pagination of large data sets as described above will be welcome and praised! I'm not opposed to using different tools, coding, etc. to get this done before the clients no longer need it.

One more requirement...I cannot do large amounts of this by hand, it needs to be reproducible by a non-IT person, after the initial setup.

Not that it matters much, but to be thorough:
vMySiteDataSet:
ID (PK)
SiteName
City
State

There is an index on State.

Sample data:
ID, SiteName, City, State
1, My first site, Fresno, CA
2, My second site, San Francisco, CA
3, My third site, Tampa, FL
4, My fourth site, Dallas, TX
5, My fifth site, London, England
...etc...do this another 124,995 times.

query:
select Sitename + '--' + COALESCE (City, 'Unknown') + ', ' + State
from vMySiteDataSet
where State = 'CA'
order by City, Sitename

This query returns over 11K results in under 5 seconds. My Access report takes over 25 minutes (possibly longer, I don't pay attention...with 100% cpu utilization) to present it incorrectly.

Please to help me! This is not classwork, it is a legitimate professional request for assistance from a contributing member of this community.

 

[fearoffours]

I can't help much as I'm much of a newbie myself but try the following for a couple of your problems:
Set the "Can Grow" property to "yes" on controls of your report where you want text to wrap.

Look at the conditional formatting dialog (In the Tools menu IIRC) to help get the coloured lines - you may need to include an additional hidden numeric field on the report, and then use conditional formatting to colour the rows based on whether that value is odd or even (or something like that!).

Hope that helps somewhat!

 

[georgedwilkinson]

Thanks, fearoffours! That really helped. You rock!

With your suggestions I was able to get my head straight and get the report I needed from Access. I didn't use the colored lines but the wrap, along with the below, really did the trick.

I addition to wrapping the text, I found the properties for the columns in the "page setup" dialog on the file menu. I had to experiment with various values of column width and my field's control width but finally came to a happy medium.

When I got my head right about the wrap, I realized that the "width" of the report "container" needed to be large enough to encompass both columns, even though I was designing just one column. Without the correct width, my header and footer was crunching off to the left side of the page in an unsightly manner.

fearoffours_reputation++ (This is C syntax indicating that I added to your repuation.)

 

[georgedwilkinson]

I also found this to hilite every other line:
http://www.blueclaw-db.com/alternate_line_shade.htm