Question Limit to fields in a record

[spiderghd]

I am developing a database that will house quite a few items for each record. Can anyone tell me if there is a limit to the number of fields per record. What we have designed comes up with 364 fields for each record. Is this possible or too much?

Thanks for your help

 

[pbaldy]

Too much. Look up specifications in Help. That many fields usually indicates a design problem, so you may want to research "normalization":

http://www.r937.com/Relational.html

 

[MSAccessRookie]

I am developing a database that will house quite a few items for each record. Can anyone tell me if there is a limit to the number of fields per record. What we have designed comes up with 364 fields for each record. Is this possible or too much?

Thanks for your help

364 Fields sounds like there could be Normalization issues within the data. Can we see a sample Table Structure (no data should be needed as long as the purpose of the Columns can be determined).

 

[spiderghd]

Here is the spreadsheet with our field names.

 

[The_Doc_Man]

That is not one table, that is at least eight or nine tables and many relationships.

Every time you have a lookup, that implies that the lookup is part of a separate table that holds only the definitions you need for that lookup.

Also, the special characters in some of those field names FIRST cannot be there, SECOND make me think that some of the items are actually conditions that can be part of a QUERY and therefore not stored. THIRD, I strongly concur with the other posters that you need to look up the topic of "Database Normalization".

You can find help in the Access Help arena using "Normalization."

You can find a decent starter article at www.wikipedia.org using keywords "Database Normalization"

You can use your favorite flavor of search engine for "Database Normalization" - but in this case, you'll get so many hits that you need to know the best possible filters. Don't bother to try to do a filtration search, just set up your browser to show you the hosting domain name and only look at entries from the .EDU (education) domain. Limit yourself to colleges and universities that you've heard of and know by reputation. Read the articles until you have read two non-trivial articles in a row that didn't tell you anything you didn't already know. A "non-trivial" article is one of at least two or three full printed pages and probably should be more than that.

Revisit your fields. Using the normalization rules, you want to look for a couple of ways to filter things.

First simple filter: Do I have enough information already elsewhere in the table to recompute this item on-the-fly? If so, then it belongs ONLY in a query, never in a table. (If the information changes over time, that's a special case.)

Second simple filter: Is the information something that will (or at least is likely to) be used for looking up something for another field? If so, you have candidates for splitting tables.

Your goal: NEVER EVER more than 255 fields per single table. And even that is likely too many. Normalize to a level of at least 3rd normal form.

Search this forum for articles on database design, some of which include a how-to for the analysis of the entities you will need to track. (I know they exist; I wrote some of them. Others of my esteemed forum colleagues have also written nice starter articles.)

Your problem is going to be totally intractible UNTIL you normalize it and break it up into meaningful and usable entities. As a starting point, why did your spreadsheet have several tabs with multiple fields listed on each? (That's rhetorical: Answer - because you already realized those entities on tab X were different in nature from the entities on tab Y.) As a first approximation, I strongly suspect you will have no fewer tables than the number of distinct tabs you had on your spreadsheet. The 255-field limit applies to each table individually. You certainly can have more than 255 fields in a database. Just not all in one table.

 

[spiderghd]

Thank you for the info.

I figured I couldn't go as high as I did with the field numbers. If you look at the attachment in my previous response you will see that I set up several tabs. These tabs represent different tables that I could make within the database.

Now that I know you can't have more than 255 fields in a single table the multi table version is the way I will be going. Setting a relationship will now be my new problem. Obviously I will need a common ground with which to set this up.

The problem I have now encountered is what to make the common ground. as yo can see they do not have common data, such as same numbers, same names, or anything of the sort.

The basic idea is that each customer has their own specification. The substances in the list may or may not be in their specification. This was why we were going to list out the different substances. Looking at the first reply's link to 'Normalization' it seems this is not a good idea.

Of course this all comes down to making sure the relationships hold up when we go to make a form for data entry. I'm starting to think this is an impossible task without programming knowledge.

At this point any suggestion as to how I might simplify this would make my job much easier.

 

[RossWindows]

Looking at the first reply's link to 'Normalization' it seems this is not a good idea.

Normalization is a great idea 99.99999999% of the time.
Based on your response, I have a feeling you need to do a little more research. Here's a quote from the first posters link:

...if creating databases is part of your job, then you're treading on thin ice if you don't have a good solid understanding of relational database design theory.

My intent is not to scare you, rather I'm just saying "first-things-first".

For me, all the books and articles did not help me until I practiced with some simple databases, then and only then did relational theory start to make sense.
So I urge you! Practice!

 

[Atomic Shrimp]

I'm with The_Doc_Man on this.

Many of those field names should actually be data, not structure - by way of simple analogy...

Imagine a table to record the weights of people's pet animals - it could be constructed with fields named like this:
PersonID, Cat_Weight, Dog_Weight, Parrot_Weight, Horse_weight, Hamster_weight, Rabbit_weight... etc

So each person would have one row in the table, with a number recorded in those columns that are relevant. But...
- What happens if a person owns two dogs?
- What happens if the number of possible pet animals exceeds the number of permitted fields?
- If a person only owns a hamster, why do we bother storing all those empty fields?

A much better structure for the table might be:
PersonID, AnimalID, Animal_Weight

So a person owning three animals would be described by *three* records in this table (they're still only one person, though). It doesn't matter whether they have three different animals or three of the same kind - we just create as many records in the table as we need to describe the data, and no more.

AnimalID can be a lookup field from a table of animal types, to keep things nice and clean, consistent and compact.

And I think this is generally where you need to go with your database - you need to work out how to store all that information about perchlorates, polyamines and PCBs as rows, not columns.

 

[spiderghd]

Thank you.

That sounds much better than what we were originally planning. I give it a shot and see what happens.

Thanks again