"Too Many Fields Defined" Error in Access

Clearly the 'append' query is returning more than the allowed maximum of 255 columns. However, that's not the real issue.

What you have, as George describes, is what is known as 'encoding data as column headings'.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

This is not unusual with data imported from spreadsheets, which despite the superficial similarity of a spreadsheet to a table, are fundamentally different in purpose, and in the way in which the data is stored, from a relational database.  The importation of data from a spreadsheet into a relational database should only be the first stage in a process.  The non-normalized data should then be decomposed into a set of correctly normalized tables. If the spreadsheet layout allows it this can be automated by the execution of a set of INSERT INTO statements; what Access calls, rather inaccurately, 'append' queries.  Only after this decomposition can the full functionality of a relational database be utilised.

You'll find an example of how suitable spreadsheet data can be recast into a form suitable for storage in tables in DecomposerDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file imports some data from Excel, and by means of the Execution of a set of INSERT INTO statements decomposes it into a set of correctly normalized related tables in which the redundancy present in the original data, and the consequent risks of update anomalies, is eliminated.  A brief description of each stage in the process is given as you step through the demo.

While, for purposes of the demo, the model into which the data is recast is a simple one, the principles employed can be scaled up to more complex models once the final model has been correctly defined by the creation of the set of related tables into which the data will be inserted.  The basic rule of thumb is that data must first be inserted into the referenced (one side) tables in each one-to-many, or more rarely one-to-one, relationship type before data can be inserted into the referencing (many side) tables in each .  Note that while many-to-many relationship types might exist conceptually in the model these are represented in the physical model by resolution of the relationship types into two or more one-to-many relationship types.  The ContactEmployers table in the demo is an example of this, resolving the binary many-to-many relationship type between contacts and employers into two one-to-many relationship types.

Also relevant is the UnencodeColumns demo in the same OneDrive folder. As its name suggests this illustrates how data which is encoded as column headings can be recast into a form where all data is stored as values at column positions.

If you open the 'append' query as a datasheet, how many columns does it return? Or does it fail to open?

I would comment that tables with such large numbers of columns (fields) are almost certainly not normalized, and in need of decomposition into multiple tables, rather than being merged into one.  If you need to see data from multiple normalized tables the way to do so is as the result table of a query, not as a new base table.

Hi Ken, thank you for your response - I have provided a bit more context in my previous post. Based on the context I described, I am not sure the process of normalization necessarily applies in my case (please correct me if I am wrong).

In the case with this query, I am unable to open it in datasheet view (due to the too many fields defined error), however when I open another query which appends data from 2 of my linked tables into another third table with 40 fields, 40 columns are returned in datasheet view.

Hi, thank you very much for your quick response.

I understand that a table with 128 may seem suspect. Within Access, I am appending data from individual (health) service databases to make one master database. Each service database is identical, and I have pre-designed tables which I am appending the data into. The tables in the Access database are mostly split up by measure so that the responses/scores can later be analysed and reported on. The table with 128 fields that I am referring to in this post has a service ID, a patient ID, a appointment ID and then a field for each item of the measure (the data in the item fields is numerical).

It may be worth mentioning that the Access database does not have a front end to support data entry - the data is acquired through submission of Excel spreadsheets. I am using Access to store and manage the data.

I hope this provides a bit more context - thanks again!

As a general rule, a table with that many fields (128) would be considered suspect to begin with. Although the theoretical limit is 255, that is an exceedingly rare situation.

So, we might profitably start with an evaluation of the table design itself. It's probably worth taking a hard look at whether this is a relational design or a "spreadsheet" design.

In addition, there is also a limit on the number of characters in a row. If that 128 field table has a number of long text strings in it, that would also push you out of bounds.

In short, we probably should start with a narrative description of the context. What is being appended to what? How appropriate the relational table design is.

Thanks for providing enough context to attempt suggestions.