A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old March 30th, 2010, 11:54 PM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default Don't understand the relationship between a combo box and a table

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
Ads
  #2  
Old March 31st, 2010, 12:09 AM posted to microsoft.public.access.gettingstarted
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Don't understand the relationship between a combo box and a table

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.

"forest8" wrote in message
...
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



  #3  
Old March 31st, 2010, 02:42 AM posted to microsoft.public.access.gettingstarted
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default Don't understand the relationship between a combo box and a table

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?

--
Duane Hookom
MS Access MVP


"Jeff Boyce" wrote in message
...
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.

"forest8" wrote in message
...
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



  #4  
Old March 31st, 2010, 02:44 AM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default Don't understand the relationship between a combo box and a ta

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.



"Jeff Boyce" wrote:

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.

"forest8" wrote in message
...
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



.

  #5  
Old March 31st, 2010, 03:10 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Don't understand the relationship between a combo box and a table

On Tue, 30 Mar 2010 15:54:02 -0700, forest8
wrote:

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.
--

John W. Vinson [MVP]
  #6  
Old March 31st, 2010, 03:32 AM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default Don't understand the relationship between a combo box and a ta

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" wrote:

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.



"Jeff Boyce" wrote:

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.

"forest8" wrote in message
...
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



.

  #7  
Old March 31st, 2010, 03:52 AM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default Don't understand the relationship between a combo box and a ta

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



"John W. Vinson" wrote:

On Tue, 30 Mar 2010 15:54:02 -0700, forest8
wrote:

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.
--

John W. Vinson [MVP]
.

  #8  
Old March 31st, 2010, 04:02 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Don't understand the relationship between a combo box and a ta

On Tue, 30 Mar 2010 19:32:01 -0700, forest8
wrote:

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!
--

John W. Vinson [MVP]
  #9  
Old March 31st, 2010, 04:10 AM posted to microsoft.public.access.gettingstarted
Duane Hookom[_4_]
external usenet poster
 
Posts: 316
Default Don't understand the relationship between a combo box and a ta

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


--
Duane Hookom
MS Access MVP


"forest8" wrote in message
...
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" wrote:

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.



"Jeff Boyce" wrote:

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.

"forest8" wrote in message
...
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


.

  #10  
Old March 31st, 2010, 04:41 AM posted to microsoft.public.access.gettingstarted
forest8
external usenet poster
 
Posts: 196
Default Don't understand the relationship between a combo box and a ta

Currently it's one field per issue.



"John W. Vinson" wrote:

On Tue, 30 Mar 2010 19:32:01 -0700, forest8
wrote:

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!
--

John W. Vinson [MVP]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:34 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.