Explanation for limit on table widths, and how to avoid wide tables...

[inglesp]

Hi there

I've searched here - and elsewhere - for an explanation of why Access limits the number of fields in a table to 255. I would guess wide tables are unwieldy to query, and so on. I've also read that database experts would consider an overly wide table (more than 30 fields?) a sign of an unnormalized data set.

I'm currently working on a database to for the results of what is, in effect, a large survey. There are around 400 questions on the survey, and the obvious way to deal with the data would be to put it all in a table with one field per question - however doing this I run up against the table width limit.

Given the nature of the data, there is no way to normalize it as far as I can see, so the only way I can think of to deal with the large data set is to split it in two, and have two tables with a one-one relationship. This is slightly fiddly, but it also seems to be rather unsatisfactory.

Is there a better way to deal with this kind of data?

Cheers in advance,
Peter

[missinglinq]

You have to set a limit somewhere, and since 255 is approximately 8-10x larger than the 25-30 fields that most experts agree is the maximum a table in a normalized db should have, it's really not unreasonable!

What you also need to remember is that this same limit applies to queries/recordsets, and hence to reports as well as forms.

You might even want to consider dividing your questions up into more than two tables, if this can be done logically.

Good luck with your project and welcome to TheScripts!

Linq ;0)>