too many fields in a table... over 255

[Keith Chabot]

I am trying to find out what the best approach is to creating an Access
database with over 255 fileds in a table. I work for an accounting firm
and they have an Interview questionnaire that they would like ported to
Access (it is currently in Word). The Word form has well over 255 unique
fields and takes 4 pages when printed on legal paper. The goal is that
with Access we will not need to reenter the basic customer information
and a seperate interview form can be created for every year. The problem
that I am getting is that the interview form has over 300 unique fields
and is provided in English and French. I am not certain how to include
all this information in seperate tables as it is all unique information.
Also the fact that I need to provide the form in English and French is a
challenge as I do not know how drop-down menus can be changed from one
language to another. Is this going to be possible?

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

The 255-limit in Access is at least 4 times what you would ever need if you
use a normalised design.

For an example of how to set up a questionairre with a normalized design,
see Duane Hookom's free download called "At Your Survey" at:
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Keith Chabot" <ke**********@yahoo.com> wrote in message
news:2I***************@news.uswest.net...

I am trying to find out what the best approach is to creating an Access
database with over 255 fileds in a table. I work for an accounting firm
and they have an Interview questionnaire that they would like ported to
Access (it is currently in Word). The Word form has well over 255 unique
fields and takes 4 pages when printed on legal paper. The goal is that
with Access we will not need to reenter the basic customer information
and a seperate interview form can be created for every year. The problem
that I am getting is that the interview form has over 300 unique fields
and is provided in English and French. I am not certain how to include
all this information in seperate tables as it is all unique information.
Also the fact that I need to provide the form in English and French is a
challenge as I do not know how drop-down menus can be changed from one
language to another. Is this going to be possible?

[Beowulf]

Keith Chabot wrote:

I am trying to find out what the best approach is to creating an Access
database with over 255 fileds in a table. I work for an accounting firm
and they have an Interview questionnaire that they would like ported to
Access (it is currently in Word). The Word form has well over 255 unique
fields and takes 4 pages when printed on legal paper. The goal is that
with Access we will not need to reenter the basic customer information
and a seperate interview form can be created for every year. The problem
that I am getting is that the interview form has over 300 unique fields
and is provided in English and French. I am not certain how to include
all this information in seperate tables as it is all unique information.
It's not possible to exceed this limit since it is inherent to Microsoft
Jet 4.0. However, in any circumstance where you are designing a
database from scratch, you wouldn't want to exceed this limit anyway
since maintaining it would be a mess.
Also the fact that I need to provide the form in English and French is a
challenge as I do not know how drop-down menus can be changed from one
language to another. Is this going to be possible?

The way this is done in the database I maintain (which is used to
specify questionnaires) is to have a separate table that holds the
question text from the main question table. There is a 1 to many
relationship between the main question table and the question text table
to accomodate multilingual surveys.

You could then have the user decide which of the languages stored in
your database's language table they wanted to see upon startup and then
load the menus with the correct language.

[Br]

Keith Chabot wrote:

I am trying to find out what the best approach is to creating an
Access database with over 255 fileds in a table. I work for an
accounting firm and they have an Interview questionnaire that they
would like ported to Access (it is currently in Word). The Word form
has well over 255 unique fields and takes 4 pages when printed on
legal paper. The goal is that with Access we will not need to reenter
the basic customer information and a seperate interview form can be
created for every year. The problem that I am getting is that the
interview form has over 300 unique fields and is provided in English
and French. I am not certain how to include all this information in
seperate tables as it is all unique information. Also the fact that I
need to provide the form in English and French is a challenge as I do
not know how drop-down menus can be changed from one language to
another. Is this going to be possible?

*** Sent via Developersdex http://www.developersdex.com ***

Normalise your table design.

eg.

Instead of....

tblMain
-----------
ID F1 F2 F3 F4 F5

Have something like.....

tblMain
----------
ID FieldTypeID Value

tblFieldTypes
--------------------
FieldTypeID
FieldTypeDesc

Then it's dynamic and you can have as many 'fields' as you need.
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response