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: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.
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
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?
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
Not that it matters much, but to be thorough:
There is an index on State.
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.
select Sitename + '--' + COALESCE (City, 'Unknown') + ', ' + State
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.