Don't understand the relationship between a combo box and a table

[forest8]

Hi there

Currently I am getting the following message:

"The operation failed. There are too many indexes on table 'Orders'. Delete
some of the indexes on the table and try the operation again."

In my table, I am trying to change a text box into a multi=select combo box.

There are 45 fields in my table of which 40 have this multi-select combo box.

I don't understand where these indexes are being created.

I do have 1 primary key in my table.

Thank you

 

[Jeff Boyce]

First, be aware that the general consensus among regular Access users is
that more than about 30 fields in a table is fairly unusual, and usually
indicates that the table/database needs a bit more normalizing.

Access adds indexes of it's own, "behind the curtain" so to speak. Have you
opened the table in design view, clicked on the Indexes button and inspected
what indices are showing?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Duane Hookom]

I would also add that most of us don't care for multivalue fields.

Do you care to share what you are storing in your table?

 

[forest8]

When I check the indexes, there are only 2 indexes created:

One is the tables' primary key and the other is the Foreign Key.

I can't seem to see any other index.

 

[John W. Vinson]

Hi there

Currently I am getting the following message:

"The operation failed. There are too many indexes on table 'Orders'. Delete
some of the indexes on the table and try the operation again."

In my table, I am trying to change a text box into a multi=select combo box.

There are 45 fields in my table of which 40 have this multi-select combo box.

I don't understand where these indexes are being created.

I do have 1 primary key in my table.

Thank you

My guess is that these combo boxes have indexes of their own which contribute
toward the form's index count.

I would *VERY STRONGLY* suggest that putting combo boxes into a table -
particularly multiselect combo boxes - is a major misuse of Access, and that
it is unnecessary!

Table datasheets are becoming more complex with recent releases of Access, but
they're still *very limited*. There is nothing that you can do with a
complicated table that you cannot do with simple, properly normalized tables,
and a well designed Form. You do NOT need multiselect combos, or any combos at
all, in your Table in order to do so.

 

[forest8]

In previous posts, I was asking for assistance in creating a Case Management
Datatabase.

In this database, there are 5 categories of investigation:

School, Community, Individual, Peers, and Family.

In the School category, there can be anywhere from one to seven actions a
youth can take depending on how he has answered previous questions.

For instance, if the youth answered that he has no role models, then the
reaction is to help the youth obtain positive role models, positive support,
and/or Empowerment but not necessary all can/would be chosen.

All this information is hopefully placed in a form in which at each phase of
their involvement in the program, all their youth's responses can be reviewed.

If I split up my form into the categories by phase, this can mean an
additional 25 tables.

This would bring my total tables to almost 100.

I can't create any more relationships since I am at the limit.

 

[forest8]

What do you mean by "simple, properly normalized tables"?

 

[John W. Vinson]

In previous posts, I was asking for assistance in creating a Case Management
Datatabase.

In this database, there are 5 categories of investigation:

School, Community, Individual, Peers, and Family.

In the School category, there can be anywhere from one to seven actions a
youth can take depending on how he has answered previous questions.

For instance, if the youth answered that he has no role models, then the
reaction is to help the youth obtain positive role models, positive support,
and/or Empowerment but not necessary all can/would be chosen.

All this information is hopefully placed in a form in which at each phase of
their involvement in the program, all their youth's responses can be reviewed.

If I split up my form into the categories by phase, this can mean an
additional 25 tables.

This would bring my total tables to almost 100.

I can't create any more relationships since I am at the limit.

How about:

Students
StudentID <primary key>
LastName
FirstName
<other biographical info as appropriate>
SchoolID <where is this student enrolled>

Cases
CaseNo <primary key>
StudentID <link to Students, who is being investigated>
<other fields relevant to the case as a whole>

Categories
CatgoryID <primary key>
Category (e.g. School, Community, ...)

Issues
IssueID <primary key>
Description <text, e.g. "lack of role models">
CategoryID <in which category is this issue>

StudentIssues
CaseNo <link to Cases and thence to Students>
IssueID <link to Issues, which issue did this student raise>

Similar tables for Responses, not sure how you want them linked.

Note that NONE of these tables need more than two or three indexes.

How are your tables structured? One field per issue perhaps, or one field per
response? That may be the source of your problems!

 

[Duane Hookom]

I think your table structures are most likely wrong. Seven actions should
create seven records. Do you understand normalization?

 

[forest8]

Currently it's one field per issue.

How about:

Students
StudentID <primary key>
LastName
FirstName
<other biographical info as appropriate>
SchoolID <where is this student enrolled>

Cases
CaseNo <primary key>
StudentID <link to Students, who is being investigated>
<other fields relevant to the case as a whole>

Categories
CatgoryID <primary key>
Category (e.g. School, Community, ...)

Issues
IssueID <primary key>
Description <text, e.g. "lack of role models">
CategoryID <in which category is this issue>

StudentIssues
CaseNo <link to Cases and thence to Students>
IssueID <link to Issues, which issue did this student raise>

Similar tables for Responses, not sure how you want them linked.

Note that NONE of these tables need more than two or three indexes.

How are your tables structured? One field per issue perhaps, or one field per
response? That may be the source of your problems!

 

[Richard]

What do you mean by "simple, properly normalized tables"?

"John W. Vinson" wrote:

How about:

Students
StudentID <primary key>
LastName
FirstName
<other biographical info as appropriate>
SchoolID <where is this student enrolled>

Cases
CaseNo <primary key>
StudentID <link to Students, who is being investigated>
<other fields relevant to the case as a whole>

Categories
CatgoryID <primary key>
Category (e.g. School, Community, ...)

Issues
IssueID <primary key>
Description <text, e.g. "lack of role models">
CategoryID <in which category is this issue>

StudentIssues
CaseNo <link to Cases and thence to Students>
IssueID <link to Issues, which issue did this student raise>


This is the primer for a normalized table, take the MVP's advice. If your
table aren't right nothing else matters.

Richard

 

[forest8]

Thank for the advice. I'm taking your suggestions into my database.

If I run into any issues, I'll start a new thread.

Thanks again.

 

[John W. Vinson]

Currently it's one field per issue.

So... when you add a new issue you need to redesign your table, change all
your queries, restructure all your forms, all your reports?

Sorry, but that design is *just plain wrong*, and is the source of your
difficulties with indexing and with form design. You're using a relational
database - use it relationally! Tables should be tall and thin, not wide and
flat; data should be stored in fields, not in fieldnames.

 

[John W. Vinson]

What do you mean by "simple, properly normalized tables"?

What I said elsewhere in the thread:

How about:

Students
StudentID <primary key>
LastName
FirstName
<other biographical info as appropriate>
SchoolID <where is this student enrolled>

Cases
CaseNo <primary key>
StudentID <link to Students, who is being investigated>
<other fields relevant to the case as a whole>

Categories
CatgoryID <primary key>
Category (e.g. School, Community, ...)

Issues
IssueID <primary key>
Description <text, e.g. "lack of role models">
CategoryID <in which category is this issue>

StudentIssues
CaseNo <link to Cases and thence to Students>
IssueID <link to Issues, which issue did this student raise>

Similar tables for Responses, not sure how you want them linked.

 

[Jeff Boyce]

OK, now try running the database documenter on the table(s). See how many
indices Access believes you have...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

The indexes you don't see are hidden and will not show up when you use the
indexes dialog. They are being created to manage the relationships being set
up by all those comboboxes used by the lookup fields.

You can detect them by using VBA code to step through the indexes collection
for the table.

This will give you the count of indexes for a particular table. Enter it into
the VA+BA immediate window.
Currentdb().TableDefs("NameOfTable").Indexes.Count

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County