DATA TYPE ERROR PLEASE HELP

[Guest]

I am designing a very large database in Access and have 100+ fields that
contain YES/NO data. When I tried to save the table, I was given an error
that said that there were too many fields defined and that the changes could
not be saved. If there is a maximum number of fields that may be defined
YES/NO what is it and if not why am I getting this error?

 

[Rick B]

Why would you have 100 fields in one table? You obviously have a design
flaw. A table should have no more than ten or twenty fields.

Access is a relational dataabse, not a spreadsheet. This means that you
build separate tables for each type of data that you want to trach and you
tie them together (or relate them) using common fields in each table.

You are simply building a spreadsheet.

Post the structure of your data and we can help you get this built properly.

 

[John Vinson]

I am designing a very large database in Access and have 100+ fields that
contain YES/NO data. When I tried to save the table, I was given an error
that said that there were too many fields defined and that the changes could
not be saved. If there is a maximum number of fields that may be defined
YES/NO what is it and if not why am I getting this error?

The limit is 255 fields; but every time you change the properties of a
field in table design view, one of the 255 "slots" gets used up. They
can be recovered by using Tools... Database Utilities... Compact and
Repair.

However, you may want to reconsider your table design! 30 fields is an
ENORMOUSLY wide table; 100+ is probably simply *wrong*. "Fields are
expensive, records are cheap" - I strongly suspect that you're storing
data (questionnaire questions maybe?) in fieldnames. A one-to-many
relationship to a second table, with one *record* per question, is a
much better design.

John W. Vinson[MVP]

 

[Guest]

John: I attempted to use the linking feature as I already had the tables I
needed. However, because there are no common fields, the relationship didn't
operate the way I needed it to. What I am trying to do is construct a
central document that will allow me to query according to each of the data
fields. At present, I have 7 or 8 different tables that are organized
alphabetically by the fields -- the problem is that when I want to do a query
that accesses more than one of those tables, the query doesn't work. Perhaps
what I need to be building is a spreadsheet (as one of the respondents
suggested) instead of an Access database.

 

[John Vinson]

John: I attempted to use the linking feature as I already had the tables I
needed. However, because there are no common fields, the relationship didn't
operate the way I needed it to. What I am trying to do is construct a
central document that will allow me to query according to each of the data
fields. At present, I have 7 or 8 different tables that are organized
alphabetically by the fields -- the problem is that when I want to do a query
that accesses more than one of those tables, the query doesn't work. Perhaps
what I need to be building is a spreadsheet (as one of the respondents
suggested) instead of an Access database.

If you're treating fieldnames as data, then indeed Access will NOT
work for you. You need to either use a spreadsheet, or "normalize"
your tables so that they are correctly structured for a relational
database, with foreign keys linking the tables and without any data
stored in fieldnames.

If you do not have common fields then you simply cannot create a
relationship; the concept makes no sense - that's what a relationship
IS, a way of linking two sets of data together on the basis of a
common field.


John W. Vinson[MVP]

 

[Guest]

Tracey,
Once you get over a half dozen Yes/No fields, a different approach can help.
Especially when you get around 100
Consider this...
Create a new table containing a numerical key and the description (what you
would put in the label of the tickbox) of the Yes/No fields
Create a second table with two fields, one to hold the primary key of your
existing table and another to hold the numerical index of the new table.
The object is that this second table contains all the YESs for each main
record, and the absence of a record infers a NO
On your main form you will need a pair of list boxes, one to hold the YESs
and the other to hold the NOs, and code to move items from one to the other
using a double click for example.
This greatly simplifies your form design and makes add a new "Yes/No" a
matter of adding a new row to your first table.
Hope this makes sense.
If you would like a working sample email me at
chris
at
mercury-projects
dot
co
dot
nz