Too Many Fields - Reports and Queries

[bhipwell via AccessMonster.com]

I have a report that pulls from a single query. This query is made up of two
tables and 9 queries. The tables include:

1) Employee information that requires about 45 unique fields
2) Company information that requires about 55 unique fields

The 9 queries are simple queries that basically group information from
various other tables including the employee and company table.

My database is normalized. Here is the issue:

The amount of information I need to display on a particular report
encompasses about 70 fields. This translates into requiring 70 fields in the
query powering the report. If I remove about 10 fields from the query, the
report runs...leave all 70 fields in the query I get "too many fields defined.
"

I believe I am hitting the 2000 byte limit per row within my query. My
questions:

1) Does it sound correct that I am hitting the byte limit?
2) If so, what may be a possible solution?
3) If not, what am I dealing with

Here is what I have tried thus far:

1) Created a subreport, splitting the fields between the main and subreport,
but both reports accessing the same query. Obviously this did not work since
it is the query reaching the field limit.
2) I created a new subreport. The main report was based on a smaller new
query, and the subreport was based on a new query with the remaining required
information. The subreport turned up blank (I checked and removed linked
fields)

Need help!

B

 

[Duane Hookom]

I would expect the main/subreports based on subsets of the fields should
work. You should have a link master/child field or fields.

 

[John Spencer]

First does the query itself run. That is forget about the report for a
minute and run the query.

The message too many fields is not the message you will get if the size
(2000 bytes) of the records is too large.

If the query fails, you might take a look at the multiple queries and make
sure that they are not using fields they don't need to give you the results
you want.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[bhipwell via AccessMonster.com]

John,

The query runs fine if I run it by itself. Thoughts?

B

 

[John Spencer]

Then the problem is not the query, but something to do with the report.

It is possible that the report itself is corrupted and may have to be
rebuilt. O U C H.

Here are Allen Browne's instructions on how to recover a corrupted mdb.

Here is a standard sequence to try to rescue a corrupted mdb

0. Make a backup copy of the file.
00. Make a backup copy of the file.

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why: http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file.
Decompile the database by entering something like this at the command
prompt while Access is not running. It is all one line, and includes the
quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are
resolved.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group,
rather than allenbrowne at mvps dot org.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[bhipwell via AccessMonster.com]

John,

I went through your list of fixing a corrupted database...no luck

I also went to the website link and tried the option where you essentially
create a new database...no luck.

A piece of information that I left out. I actually have two reports that are
nearly identical. Each of them is powered by their own query, both of which
are identical. The only difference between the two queries is they filter
based of information that can be inputted on different forms. Both queries
work but both reports produce the "too many fields defined" error. This is
what leads me to believe that the query itself (although working by iteself)
is causing the error.

My next step is to rebuild the report, although I don't have much confidence
this will work due to my reasoning above. Thoughts?

 

[bhipwell via AccessMonster.com]

John,

Just thought of something. My query (although running fine) is created from
two tables and nine other queries. If you add up all the fields between
these 11 items and the tables that power the nine queries within the query, I
am sure I am exceeding the 255 limit.

Could this be a cause of my problem?

One of my tables contains a large number of fields, probably around 70.
Obviously this seems like a large number of fields for one table, however,
each field is unique to each record, therefore splitting the table into
smaller ones would not solve the problem. Totaling the fields required in
each smaller table would still equal the number I have in this central table.
(if not a few more since we would add new primary keys).

The large table contains employee information including:

Lastname, Firstname, MI, Gender, Social, Street, City, State, Zip, County,
Phone, WorkPhone, Email, MaritalStatus, FLSAStatus, UnionStatus, Birthday,
HireDate, EmploymentStatus, EmployeeClass, Department, HealthElection,
DentalElection, LifeElection, VolLifeElection, VolSpouseElection,
VolChildElection, VolSTDElection, STDElection, LTDElection, VolLTDElection,
FSAParticpant, HSAParticipant, DepFSAParticipant, 401kParticipant, and so on
and so forth.

As you can see, the answers to each would be unique to each individual record.
For example, the HealthElection has a relationship with a separte table that
would reference the monthly premium, payroll deduction, etc. So this is how
the database is normalized.

Anyhow, looking for input. Tomorrow I am going to try to rebuild the reports
to see what happens. Advise before my project would be greatly appreciated.


Thanks!

B

 

[bhipwell via AccessMonster.com]

John,

Rebuilt the report. Didn't work. Looks like a query problem.

B

 

[John Spencer]

I am only suspicious of the report because you said the query for the report
runs fine. That seems to indicate that the problem is with the report and
not with the query. A really simple test would be to build a report with
just a few fields that uses the query as it source and see if that works.
If it does then that seems to again point to a problem with the report
itself and not the query.

If the test report fails then I suspect the interaction between the report
and the query is causing the problem. The field count should only involve
the field that are in the select and where clauses of your queries. Your
solution may involve using a temporary table to hold the data you are
calculating in the subordinate queries.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[bhipwell via AccessMonster.com]

If I delete 9 fields off the report, the report works. Therefore it seems I
am exeeding some limit of the report or a combination of the report and query.
I am reworking my query to improve some ineffeciencies. I'll keep you posted.


B