How to output 164 Fields?

[wdsnews]

Our school district needs a table that is 164 fields long with very specific
formatting. I began by building a query, but ran out of fields after the
16th. How can I build output from relational linked tables to include all
the fields necessary?

 

[Guest]

You don't. You create a relational structure driven by the data. Later when
you need a report in a specific formatting, you use things like Crosstab
queries plus sorting and grouping to get things to look right.

Access can handle a maximum of 255 fields; however, these fields can only
hold 2,000 characters (discounting memo and ole fields). Chances are that you
have too much data for a single record. It's also possible to 'burn up' some
of the 255 fields by doing such things as creating a field then deleting it.
Still that usually happen well after only 16 fields.

 

[John W. Vinson]

Our school district needs a table that is 164 fields long with very specific
formatting.

No, it doesn't. Such a table would be horribly non-normalized. If you're
assuming that you must use a table datasheet to view data, revise your
assumption, because it's incorrect!

I began by building a query, but ran out of fields after the
16th. How can I build output from relational linked tables to include all
the fields necessary?

How did you "run out"? A Query can have 255 fields (but with rare exceptions
it never should).

John W. Vinson [MVP]

 

[wdsnews]

Well, I pulled out my credit card, agreed to pay $259, and discovered it was
a bug. According to Microsoft's tech support, there are circumstances where
the query design dialogue only provides 16 fields.

 

[Guest]

You mean that I was wrong yet again? Well at least you got more of your
money's worth spending that $259 than with my free advice.

Still with that many fields the table is going to be horribly structured and
not normalized.

 

[wdsnews]

Hi John,

I appreciate your attention. Actually, I do need query output that is 164
fields wide.

What I found out by giving my credit card to Microsoft is that Access has a
bug in which there are circumstances where the query won't automatically
provide additional fields beyond the initial 16. However, they gave me a
work around and I gave them $259.