To many fields Access 2003

[JediPhantom]

Hello..I have a query that is used to generate data that would be mail merged to create a large word document (form). The problem is the query has grown over time and now pulls data from 7 tables with over 255 fields!!..I am now getting the message "to many fields defined". Is there a way around this?..I have been told its possible to copy parts of the data to the clipboard i.e break up the data and then merge into Word. However it may just be this application has outgrown Access. Also improper design was my first thought, but each of the fields is a requirement for the Word document, are there any other possible solutions?

thanks

 

[plog]

improper design was my first thought

Its also my first thought, and 2nd through 5th thoughts. Can you post some sample fields and data? Maybe a screenshot of the query in design view.

Even if it is improperly structured, one thing you can do is combined relevant fields. Suppose you have fields in your query for City, State and Zip; but in your document you always use those fields together. You could combine them into one field in your query:

CityStateZip: City & ", " & State & " " & Zip

Remove the 3 individual fields and you have a net loss of 2 fields. Check your document for any instances like this.

In that same vein, you coudl even make a field in your query contain a whole paragraph's worth of text to save yourself a few fields. Suppose this was your document:

On [EventDate], you made [DepositCount] deposits totalling [DepositAmount], making your balance [Balance].

You could make that entire paragraph a field in your query:

BalanceParagraph: "On " & [EventDate] & ", you made " & [DepositCount] & " deposits totalling " & [DepositAmount] & ", making your balance " & [Balance] &"."

 

[JohnLee]

Hi,

There is a limit to the number of fields you can have in tables/queries which is 255, I've found that you can't in fact have 255 fields in a table or querie because Access starts to generate errore messages when it starts to get to 225 fields, it generates messages like "too many fields defined"

That said it does appear that there are design issues here, because you shouldn't really be getting to that many fields in your table/queries, so perhaps you need to re-evaluate the database structure and carry out some normalisation/re-design where appropriate.

Once you've done that you may find that what your trying to do will be more archievable.

Regards

John