 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / August 2004Multiple Indexing
I am trying to index 10 fields under one index, I have read and followed the steps on the help files for creating a multiple field index however it does not appear to be working; the fields will allow duplicate data without any error messages at any point. I have tried deleting fields from the index to determine the maximum number it is capable of working with (although it specifies this is 10) however it does not seem to work with any number greater than one. I have also tried downloading your updates however I cannot install service packs 2 and 3. Can you please help me. If I understand your post correctly it sounds like you are askin indexes to do something they were not design to do. An index is suppos to provide quick access to data not validate if duplicate record exists. An index of 10 fields is a lot and this will seriously affect th performance of your database. If you think about it every time yo update/insert/delete a record in your database ten database fields wil have to checked/added/deleted to and from the index. A better approch would be to set up some validation rules for eac field to check for duplicates. Or if these fields are the primary key in which case I would say you may need to redesign your table ) the set these fields up as unique and use an surrogate key with data_typ of auto_number. > *I am trying to index 10 fields under one index, I have > read and followed the steps on the help files for [quoted text clipped - 11 lines] > > Can you please help me. - sajohn5 >> *I am trying to index 10 fields under one index, I have >> read and followed the steps on the help files for [quoted text clipped - 6 lines] > to provide quick access to data not validate if duplicate records > exists. Absolutely not true: a unique index is an important part of the business rules incorporated into the db design. It is exactly that: a contraint to prevent two records having the same value(s) in the field(s). A Primary Key is always a Unique Index, but there is no reason not to have other unique indexes too. > An index of 10 fields is a lot True: but if that is dictated by the real-world that he is modelling, then there is not much choice. > and this will seriously affect the > performance of your database. Which would you buy: a slow database or one that screws up the data? > A better approch would be to set up some validation rules for each > field to check for duplicates. Can't check for uniqueness in a validation rule (at least, not in Jet) See duplicate thread for details on setting up Unique Index with Ignore Nulls or Not Ignore Nulls. B Wishes Tim F |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.