"too many fields defined" error when opening a report

[neeko1226]

I have a Access 97 report that pulls in 178 fields from a query as the record source. there are 40 labels in the detail and 25 labels in the Page Header. In addition, I have 150 text boxes that contain formulas using data from the record source. The query opens fine (all 1045 records display with all 178 fields), but when I attempt to open the report I get a "too many fields defined" error. I was under the impression that I could define 700+ fields on a report without running into this issue.
Can anyone explain why I'm hitting the limit here? Is there a way for me to have this much info on a report? If not, anybody have another solution?

 

[jsteph]

Access does some behind-the-scenes query building--so really the sql that's actually running behind the report is only using the 'RecordSource' query or table for the report as a starting point. So my guess is that that's where the error comes from.

As has been mentioned here many times before--often when one get's anywhere near the 255 field limit--the design of the table or query may have room for improvment. Possible a crosstab query may help. Can you give any details on what type of info the report will be showing?
--Jim

 

[neeko1226]

I have 55000 loan records. Each has a Branch code and a Lender code which is what I have to group the report by. For each group I have to calculate the following columns:
Total Loans/Tot with Ovg Points/Tot with Udg Points/Mean of those with Ovg Points/Mean of those with Udg Points/Median of those with Ovg Points/Median of those with Undg Points. In addition, each of those seven columns is broken down by 25 different rows (ie Race, Ethnicity, ect.).

Currently, I have functions to calculate each coulmn based on the recordset it's passed. I put the totals by row category in one table, the median by row category in another, and Mean by row category in a third. The tables are related by a fourth table containg the run date branch code and the lender code (which combine to create a unique value). The record source for the report is a query combining the three data tables with the pass through table. giving me 178 fields (25 per column * 7 columns + 3 relational fields from the pass through table ...rundate, branch code, lender code = 178)

Most of the fields in the tables are formatted as double.

Not sure if that confuses you further or gives you a better view of what I'm doing.

 

[dhookom]

Most of the 25 different rows sounds like subreports based on totals queries. You should be able to calculate much of this on the fly within your report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]