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. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Don't understand the relationship between a combo box and a ta
"forest8" wrote:
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 |
#12
|
|||
|
|||
Don't understand the relationship between a combo box and a ta
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" 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] . |
#13
|
|||
|
|||
Don't understand the relationship between a combo box and a ta
On Tue, 30 Mar 2010 20:41:02 -0700, forest8
wrote: 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 [MVP] |
#14
|
|||
|
|||
Don't understand the relationship between a combo box and a ta
On Tue, 30 Mar 2010 19:52:01 -0700, forest8
wrote: 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. -- John W. Vinson [MVP] |
#15
|
|||
|
|||
Don't understand the relationship between a combo box and a ta
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. "forest8" wrote in message ... 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 . |
#16
|
|||
|
|||
Don't understand the relationship between a combo box and a ta
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 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 . |
|
Thread Tools | |
Display Modes | |
|
|