A lot of fields... (1 Viewer)

[Haynesey]

Hi,

I am setting up a table based on a questionnaire where there is a genuine need for a lot of fields. However, I have got to the stage where I can add no more fields in the table.

How can I add all the fields I need? Can you carry on in another table and them join them in a query somehow?

Thanks in advance

Lee

 

[Dennisk]

I have this problem with large Health service datasets. Use another table with a one-one relationship.

 

[Haynesey]

Thanks

Thanks

Cheers

Lee

 

[neileg]

Isn't this a normalisation issue? Lots of fields should become lots of records in a related table.

 

[Dennisk]

Neil,

That is not the case, as some clinical investigations or surveys ie adult/child obesity may result in in several hundred data items all of which would would be related to a particular referral. As you can only store 256 columns in any one access table you require more table space with a one to one link. Basically its a continuation table.

 

[neileg]

Dennisk

Yes, I did understand the nature of the application. But I can't see any reason why your fields can't be replaced with records. I'm not being precious about this, I'm not a normalisation fanatic, it just seems there's a better way.

 

[The_Doc_Man]

When you reach this problem, it is ALWAYS AND ONLY a bad design issue. You have chosen the wrong viewpoint.

Tables can be wide but shallow or narrow and deep.

The former puts 255 fields in a row and then links using common PKs per record.

The latter puts 255 rows with a respondent ID, question ID, and answer. Then you have as many questions as you need.

How you do reports is up to you, but you can't display 255 fields on a single-line-per-record report anyway.

And I don't care whether you think I'm being persnickety or not. If you don't want to be stuck in Excel for the rest of your life, rethink this design and realize that there are other ways to skin cats. <MMMMEEEEOOOOOWWWWRRRR!)

 

[aikea]

There is something called Normalisation.

For example if you record a person and his disease type, you should have two tables. One for the detail of that person (age, gender etc), one for the disease type recorded. The person will give a field named "diseaseTypeID" which contains the "ID" of his or her disease type.

You don't have to put everything within one table. I have been working with Access for 3 years hardly use any table with more than 10 fields.