how do I create more fields in access

[Guest]

I have created a rather large database and have reached the 255 field limit.
Is there any way to increase the field limit. I have compacted the database.

I am using Microsoft Access 2003

If this is not possible is there anywhere I could look at hints and
suggestions. The database is based on a questionnaire with Yes/No and tick
box answers and as I said is quite long.

Many thanks

 

[Craig Alexander Morrison]

Use Excel!

....or design your database according to relational database design
principles. You have created a large bucket of a table not a database.

One of which is to remove repeating groups of data to other tables in a 1 to
Many relationship.

Tables should contain data about one subject any subjects within subjects
should be in a separate table in the above 1 to Many relationship.

To get the best out of Access you have to unthink Excel. I would expect that
you could have a Question table containing each Question, 1 record 1
question and that would link on the many side of a 1 to many relationship
with a Questionnaire table which had details about the questionnaire in
general.

I trust others will point you at some adequate resources on database design.

Good Luck.

 

[Rick B]

If you have more than about 30 fields in any given table, then your design
is wrong.

Post back and explain the structure of your database, and we can help you
'normalize' it.

You should be creating a bunch of small related tables, not one large table.

 

[George Nicholson]

Use Excel!

Which also has a 255 field/column limitation

 

[Craig Alexander Morrison]

Oops! I loaded the OWC Spreadsheet, which does not have that limitation.
Excel has 256 columns, column 1 is 0 if you know what I mean.

Well I am hardly going to say use SQL Server, it has a 1024 limit.

Or you could pivot the data if you only had 256 records.

In summary: Ooops! Then they should go with the alternative, an option they
can't refuse.

 

[Guest]

I have a table with around 100 or so fields. What is wrong with that,
assuming I will not have to add more than 155 more fields?

 

[Rick Brandt]

I have a table with around 100 or so fields. What is wrong with that,
assuming I will not have to add more than 155 more fields?

It's not a question of hitting the limit. That is only a peripheral
concern. In a properly designed relational database each field describes a
single attribute about the entity represented in that row and it is just
VERY unusual for an entity to have that many attributes. Therefore when we
see a table with more than 30 or so fields it is almost inevitable that the
design is wrong.

The most common mistake is to embed a one-to-many relationship within the
table instead of using two tables. Take this table stgructure for
example...

STUDENTS
FirstName
LastName
BirthDate
FirstHourClass
SecondHourClass
ThirdHourClass
etc...

There is a one-to-many relationship between Students and the Classes they
take. This shoud be modeled in two tables not one. While this example is
fairly obvious, most people who claim that they need a really wide table are
comitting the same kind of mistake in their structure.

 

[Guest]

Thank you all for your response.

I think I have gone wrong by making the database exactly like the
questionnaire ! Most of the questions are in a tick box format:-

House Insurance Work Transport Contact

Yes
No
Sometimes
Not Applicable

I have created a separate field for each possible answer. Sorry, I can hear
you all screaming at me NO !!! This is the first time I've created such a
database. I'm thinking that now I need to create a drop down box insteading
of having 20 fields for one question I would only have 5. There are a total
of 30 questions. The only problem I see with the drop down box is sometimes
more than one box is ticked i.e. House - Yes and Sometimes (not a good
example). The data has to be analysed and I can see problems there. I'm in
a mess !

Thanks
Christine

 

[Guest]

OK now thinking the questionnaire is in sections - I could split into
sections and have more than one table and use the "relationship" to link
them. (as you already pointed out).

 

[Ed Warren]

As a statistician, married to a university research professor, I have seen
this problem in research students frequently.

1. I strongly recommend NOT Allowing 'multiple' answers to the same
question.
2. If you are already there, then create a unique code for the multiple
response and apply that code universally to all the records.
3. Start at the End and work backwards. (End --> Data
Interpertation -->Statistical Package --> InPut Format (spreadsheet) -->
InputFormat (Database) -->InputFormat (paper Forms)
4. Buld a 'dummy set' of data to work through the process, just to find out
where the snags are (you will be frequently surprised that you need to Count
where you had thought you wanted to average, etc.)

The 'real' question is: What are your really trying to do?

1. Build an electronic database so people can fill out a questionaire at a
computer and will have seveal thousand questionaires. (msAccess problem)
2. Enter data from A paper questionaire then analyize the data (excel
problem)
3. Build an electronic form you can email to people to complete and return
(XML Problem, msInfoPath, AdobeForms, msWord)
4. Build an electronic form posted on a web page for people to fillout
(msAccess with webPages (asp etc.), or Java, or ......)


In any case you have
ResearchSubject -->Questionaire -->Question --> Response

So your final data table for analysis should be three or four fields wide
(four if you have more than one questionaire to analize) maybe a couple more
if you need to capture date or other stuff.

Subject Questionaire Question Response
1005 1 1 0
1005 1 2 5
1005 1 3 2
------------------------------etc.------------------
1006 1 1 1
1006 1 2 3
1006 1 3 3
-----------------------------etc.-------------------
etc.

Lots of luck

Ed Warren