Number of Field Limitation

[canadaboy]

From my experience, Access has a limitation on the number of fields for an individual table (255).

General Questions:
Is this accurate? If so why? Query time performance? Any work arounds? Does this limitation apply to queries as well?


My Specific Application:
I have created a database of individuals. There are thousands of pieces of information (fields) for each person, and which apply only to that person. What I have done is created several tables with a one-to-one relationship. This has worked, but has made the management of queries, forms, and reports oppressive. If I could have all of these fields held in one table I believe that my database objects would be significantly consolidated. Do I have a false belief?

Any thoughts?

 

[Rich]

Your description suggests that you are trying to re-create a spreadsheet within Access and not a normalised structure.
Search here for articles on normalisation
This may help you get started
http://www.microsoft.com/office/using/column06.asp

 

[canadaboy]

Thanks for the pointer to that list of articles. I hadn't seen that before.

However, my data structure does not repeat information from field to field. The only duplication is the field that links the tables together in the relationships. I actually have thousands of fields that hold data unique to that person. In my opinion the data all belongs in a single table, but I run out of fields (255 max) too soon. That's why I had to create several tables with a ONE-TO-ONE relationship, all linked back to the table that I think all the data should be in.

Perplexed,

 

[Rich]

You don't need a field for every description, one field which has say the description and another that defines say the description type and another that identifies the person associated with the description.
It's the same as one company can have thousands of products, and many companies can have several thousand products between them. It only takes a few tables and fields to handle the entries.
Have you looked at any of the sample db's supplied with Access to see how they're structured.

 

[canadaboy]

Rich,

Thanks for your patience.

Yes, I've looked at some of the databases supplied with Access, but maybe not the one I need. Or, I may just have to think about this for a while.

I follow your company/products analogy, but I haven't been able to map it into what I'm trying to do. I'll go and ponder on what you wrote. Then I'll let you know what happened, and then I'll resign myself to my own ignorance.

Thanks,

 

[canadaboy]

I'll work on trimming down my database to post it if I need to (and thank you for the offer), and I'll have to get permission to post it, BUT.....

I will make the following comparison.....

My database holds more than 255 pieces of information about a person. This could be the person's height, weight, address, toenail color, hair color, and on and on. I want each person (record) in my database to contain this type of info (fields), but I have more than 255 pieces of information.

Does this scenario change any of the feedback that I've received to date?

 

[canadaboy]

Pat,

Thank you for your blow-by-blow explanation. I tried it out on a mini database and I now understand. Thanks to everyone for their responses.

It took me a while, but I'm much better off. Thanks again.

End of thread.