A97: "Record to large" error when printing report

[RonMcIntire]

PURPOSE
My goal is to print a directory listing containing the chapter organization name and address and 21 chapter officers with name, address, phone and email on one page of a report for each of 61 chapters. All the data is in one large record including the chapter name and address.

DATA STRUCTURE & APPROACH
I have created the printed record by filtering the appropriate officers (Pres, VP, Sec, Treas, etc.) from one table into 21 separate queries then concatinated those queries and the Chapter information is into one large query. The relationship is created solely in the queries.

PROBLEM
The record apparently exceeds the 2000 character limit of Access 97 yielding the error message "Record is too large".

POSSIBLE SOLUTIONS:
1. i can strip out the organization name and address data but I must print it with the list of chapter officers. The problem is, I do not know how to print a information that has a relationship to a record but is not included in the record.

2. I can cut the list down to 17 or 18 officers but it would limit my directory to some extent.


Is there a simpler way of printing a "record" larger than 2000 characters in A97?

Ron

 

[KenReay]

Hi

I do not undertand why you have to put all the data in one 'record', why not treat each Chapter Officer as a data row (ie a logical 'line' on the report), and in the report Group by Chapter, with each chapter beginning on a new page (this is a property of the section header and/or footer).

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RonMcIntire]

Ken:

Thanks for your response. It appears that I may not have made myself clear. Let me try again to clarify my situation.

1. The parent data comes to me in two tables: one table, called tblChapter_Officers, contains all the chapter officer names and addresses (approximately 700 names), and one table, called "tblChapters," contains all the chapter address information (61 chapters): location, phone number and meeting times. There is practically no chance that the chapter officer table will grow to more than 1000 names. Almost all of the data is non repetitive, i.e. one man only serves on function in one chapter. There are only a few occassions where one man might fill two or more positions but they are rare. I want to use Access to print a directory (report) of chapter officer names and address without changing the table data.

2. I'm not sure I fully understand your statement ". . .treat each Chapter Officer as a data row (ie a logical 'line' on the report)." If you mean that each row of data in the report should contain the name, address and phone number of one officer, I do that.

3. Ordinarilly I would agree that grouping on the Chapter would make sense. However, my approach essentially creates a flat file of data by filtering for each chapter office and joining these with the chapter information in one record so grouping on the chapter doesn't make sense to me. I guess I felt that my approach was simpler than creating what I perceived as a complicated mesh of relationships and additional tables.

4. It now appears that this method, while simple, is subject to the 2000 character limit per record and I must face reality and search for another approach.

(a) Is there a way to pull the chapter information out, and somehow link it to the report external to the big query?

(b) How about splitting the large record into 3 separate records say, 1/3 from President to Secretary, 1/3 from Treasurer to Membership and the last 1/3 from Youth Outreach to District Delegate, then link them together to print the entire listing on one page? If I use a query to concatenate the separate records, I wind up where I am now.

(c) Would creating formal relationships (from the Tools menu) help in any way?

As you can tell, my knowledge of Access is limited.

Ron Mc

 

[KenReay]

Hi

You say:

"1. The parent data comes to me in two tables: one table, called tblChapter_Officers, contains all the chapter officer names and addresses (approximately 700 names), and one table, called "tblChapters," contains all the chapter address information (61 chapters): location, phone number and meeting times. There is practically no chance that the chapter officer table will grow to more than 1000 names. Almost all of the data is non repetitive, i.e. one man only serves on function in one chapter. There are only a few occassions where one man might fill two or more positions but they are rare. I want to use Access to print a directory (report) of chapter officer names and address without changing the table data."

You do not actually say which Column in the two tables may be used to relate them, but I assume it is a ChapterId?

All you need to do it to create a query which joins the two tables on ChapterId, (or whatever the common field is), no need to be creating new tables or changing the structure of existing tables, then you base your report on the query

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RonMcIntire]

Ken:

Joining the two tables on the chapter ID is basically what I'm doing now although I'm creating several intermittent queries to filter the Chapter officer information and to format the data so it will appear correctly in my report. The resulting record with the 21 chapter officers names and addresses is what's exceeding the 2000 characher limit of Access.

POSSIBLE NEW APPROACHES
1. I haven't considered creating an "expression" to do the formatting at print time rather than doing it in a query. I'm not sure if this approach will save any space inside my record and it seems like more work.

2. Could I treat part of the data as a subreport? I tried this approach in a very rough test but it didn't work as I'd hoped so I didn't persue the idea.

Happy New Year,

Ron
Ron

 

[KenReay]

Hi Ron

Unless your data is more complex than it appears from your explanation I just cannot understand what the problem is.

Would you care to EMail me a sample of your data?, just (Say) two chapter records and their associated Chapter Officers, it does not even have to be real data if confidentiality is an issue

kenneth.reay@talk21.com Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RonMcIntire]

Ken:

Your offer to view my db is most generous but, as it turns out, while I was preparing to send it to you I found that I had accidentally created a duplicate query that must have been adding to the total character count. When I corrected it, my problem was solved. It still uses almost all the 2000 character space but at least it works.

I recognize that no self-respecting Access programmer would ever strong-arm this project the way I have but my goals are achieved and I can now move on. Given more time, I will take a second look and maybe try another approach. I've got lot's to learn.

Thanks for your help and your offer to provide visual assistance.

Ron McIntire