More than 255 fields management in a Select query of access 2000

[lranganathan]

Dear sir/madam

I have two tables linked together with one-one relationship with primary key.
The form has been created using a select query using these two tables. total fields at present is 253(table1=25 fields, Table2=228 fields Total = 253 fields)

Now my problem is I have to add 20 fields more to the above. so the total fields now is 273 fields but it is not accepting and giving the error as "Too many keys defined 3190".

how to solve these problem

Thanks in anticipation
L.Ranganathan

 

[ColinEssex]

Your database design is wrong.

There is no way a table needs any more than say, 20 fields max.

You need to brush up on "normalisation"

I would suggest you are designing the tables like a spreadsheet and not a relational database. For example, if you wanted to record test results, you could have these fields

Test1. . . . . Test2. . . . . .Test3. . . . . . . .Test4. . . . . .Test5
20. . . . . . . . . 14. . . . . . . . 12. . . . . . . . . . 18. . . . . . . .22

This is wrong.

You should have

TestName. . . . . .Result
Test1. . . . . . . . . 20
Test2. . . . . . . . . 14
Test3. . . . . . . . . 12
Test4. . . . . . . . . 18
Test5. . . . . . . . . 22

That way, you have only 2 fields for as many tests as you like. They are all linked back to the person via the ID number

Col

 

[brickelldb]

What if you had a table that had 255+ fields because it is absolutely necessary? Or if you had 4 tables with 75 fields because it is absolutely necessary (there are 75 questions needing to be answered in each form).

And then you want to write a report showing the answers for all 300 questions. When you query these 4 tables, your result is 255+ fields, how do you go about this?

 

[brickelldb]

Or in your example with the tests....what if there are 255+ tests with 1 question (or 2 questions) each. How would you query these results to bypass the 255) mark......had a simliar question, figure I'd ask in this (very old) thread. THANKS.

 

[boblarson]

What if you had a table that had 255+ fields because it is absolutely necessary?

I would say that "absolutely necessary" is a relative term. Is it absolutely necessary because you can't think of another way to do it? What would be the situation?

Or if you had 4 tables with 75 fields because it is absolutely necessary (there are 75 questions needing to be answered in each form).

Again, it would appear that the design is flawed in that you are using columns for things that should be rows of data.

And then you want to write a report showing the answers for all 300 questions. When you query these 4 tables, your result is 255+ fields, how do you go about this?

You could use sub reports. But, in reality it would be better to redesign it to be a correct Relational database model.

 

[gemma-the-husky]

i think what bobs saying is to have the table designed something like this

candidate (multiple candidates)
testno (multiple tests)
questionno (multiple questions)
response (the answer in whatever format makes sense)

so its now easy to get most results you want
eg all answers by candidate 1, by test
or all answeres for question 4 on test 2
or total scores by candidate for test 8
etc etc

no union queries to worry about, etc

 

[Rabbie]

When I first came to this forum one of the best bits of advice I got was
"Access tables should be tall and thin not broad and short". This means they should have relatively few fields and many records where necessary.

Remember that Access is a Relational Database Tool and not a spreadsheet program like Excel

 

[brickelldb]

I have a form with 362 items - which are either text fields for entering brief explanation or a checkbox for everytime the inspector reviews. This form has 8 sections which I ended up breaking up into 8 different tables with respective forms. But I now need to reproduce the info onto a report and can not do so because when i go to pull the query linking all 8 tables - i need to pull more than 255 fields......

 

[brickelldb]

Section1ID...InspectionID....Field1...Field2....Field3...Field41

Section2ID...InspectionID....Field1...Field2...Field3....Field56

Section3ID...InspectionID...Field1....Field2...Field3....Field38

And so on......So the 8 tables/sections make up the entire report.....get the idea...?

 

[boblarson]

get the idea...?

Yes, bad relational database design. You've designed a spreadsheet application in a database shell.

You seriously need to read this (and understand it) before continuing:
http://www.accessmvp.com/strive4peace/Access_Basics_Crystal_080113_Chapter_03.pdf

 

[brickelldb]

OK.....thanks for the read......so i guess my problem is the table structure of inputting data into the tables......so then would this be a more appropriate structure.......

Tbl_Section1
--------------
Section1ID.....VisitID.....FieldName....Entry

And then 41 rows for every section1 entry (41 questions).......

 

[boblarson]

Actually, you don't need separate tables for each section either. You would have a field to identify which section it belonged to. This is kind of a barebones design as I don't know what actual entities you have or their attributes. But hopefully this will give you an idea:

PK - Primary Key
FK - Foreign Key

tblEntries
EntryID - Autonumber (PK)
VisitID - Long Integer (FK)
FieldID - Long Integer (FK)
SectionID - Long Integer (FK)
EntryValue - Text

tblSections
SectionID - Autonumber (PK)
SectionDescription - Text

tblVisits
VisitID - Autonumber (PK)
VisitDate - Date/Time
InspectorID - Long Integer (FK)

 

[boblarson]

The big key is to design the database based on the DATA that you will be storing and retrieving, not based on your data input. You can always come up with a creative way to input the data into a correct design but trying to get data out in a meaningful way from a design based on User Interface is almost always going to be a major pain.

 

[Mike375]

Here is an easy way that in principle will apply to about any data base.

Lets say you have a DB for personal contacts and one set of details you keep is the children each of your contacts has and the details of each child such as gender, age, name etc.

The wrong way would be to have fields in a table like

FirstName1, Age1, Gender1, School1 followed by FirstName2, Age2, Gender2, School2 and etc.

Instead you would have a second table that contained the childrens details. Now if one of your contacts had no children there would be no related entries in the children's table but if one of your contacts had 6 kids then there would be 6 related records in the childrens table.

 

[brickelldb]

I understand. However......and maybe I'm just not seeing an example that relates to my situation.......what if for the details of the children, you had 255+ different details that you needed to record and produce onto a report......at the end of the day you would have 255+ fields per record (child) that you need to query....at this point would be stuck in having to do subreports to get them all onto a report.

I dont mean to beat a dead horse, I really am trying to understand/grasp the appropriate way of doing this. Any help from anyone "not sick" of this guy (LOL) really is greatly appreciated.

 

[gemma-the-husky]

255+ different details

if you REALLY had over 255 individual details about a single entity. and this was normalized, the only thing you could then do is have 150 in one table, and the others in another table, with a one to one join. (but a questionaire isnt that sort of animal - although a human genetic dna map may well be, for example)

yoo still may have presentation issues selecting more than 255 to show on a given form together, but generally not all the fields would need to be required together.

 

[boblarson]

Actually, you wouldn't have 255+ FIELDS, but 255+ pieces of data which are more likely RECORDS (rows) and not fields (columns). That is different.

If you take a look at the attached sample that I have, you can see kind of the idea where you can store multiple pieces of information as RECORDS and not as fields:

(I created this quite a while ago so it doesn't use similar data to yours)

 

[boblarson]

Sorry, wrong attachment. I have to see if I can find the right one.

 

[boblarson]

Found it.

 

[brickelldb]

Bob - great example. I get it now.

EVERYONE - THANKS - GREAT GROUP EFFORT - YOU BEAT IT INTO MY HEAD.