How to create an index with more than 10 fields?

[Lydia]

Hi,

I have a table with 12 fields, and change in any of the field could make a
new unique record. To prevent duplicate records from being entered, I need to
set up an index based all the 12 fields, however, access won't allow that. Is
there a way to work around it? thanks.

 

[BruceM]

I'm not sure what you are describing. It is always the case that a change
in any one field from one record to another will result in a new unique
record. Could you be more specific?

 

[Lydia]

My point is : all the 12 fields together combine to make a unique record. So
I need an index on all these 12 fields. Thanks.
--
Lydia Liu
Access/VB Programmer

I'm not sure what you are describing. It is always the case that a change
in any one field from one record to another will result in a new unique
record. Could you be more specific?

 

[John Spencer]

The maximum in Access (with JET - native Access database engine) is 10 fields
for an index. Sorry, but there is no exception to that limit.

Your only choice would be to check the database for duplicate records when you
enter a new record or modify an existing record.

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

 

[Jerry Whittle]

Another option would be to concatenate all the data in these fields into one
field. Then index that field. Of course this would break a couple of Normal
forms.

Speaking of normalization, is it possible that your table structure is the
problem? Do you have a lot of repeating data which could be put into another
table?

 

[Lydia]

It may be a good idea to concatenate all the data into one field and then
index it, at least for the purpose of checking on the uniqueness of the
record. I like it when I can be offered a different perspective.

I hope Access could somehow change this 10 fields limit some day.

The table holds only necessary information to define an unique engine, there
is no repeating data, but thanks for checking on it.

Thank you and all for trying to help out. I will let you know if I can use
the above idea to work it out somehow.

 

[Tony Toews [MVP]]

I have a table with 12 fields, and change in any of the field could make a
new unique record. To prevent duplicate records from being entered, I need to
set up an index based all the 12 fields, however, access won't allow that. Is
there a way to work around it? thanks.

What kind of data has 12 fields that must be in the same index?
Possibly we can suggest some alternatives.

Tony

 

[Lydia]

I would definately like to hear about your alternatives.
--
Lydia Liu
Access/VB Programmer

 

[Tony Toews [MVP]]

I would definately like to hear about your alternatives.

Please explain what those twelve fields are.

Tony

 

[Lydia]

EngineID: Primary Key, Autonumber
EngineFamilyID: Foreign Key from EngineFamilyTable
EngineCode: Like EER
ModelYear: Like 2009
8thPosVin: The 8th position of Vin
CylinderLayout: like V6
EngineSize: Like 2.7
FuelType: like Gasoline/Electric
Aspiration: Like naturally aspirated
#ofValves: like 2
ValveTrainType: Like DOHC
FuelDelieverySystem: Like PFi
EngineVariance: Like PZEV
--
Lydia Liu
Access/VB Programmer

 

[Lydia]

Hi,
I added a field called UniquenessChecking to the table and added it as an
unique index. On the data entry form, I added a text box with its control
source equal to all fields on the form concatenated. And in the beforeupdate
event, set the value in text string to the UniquenessChecking field. Then
whenever a duplicate record is entered, the system will give out a messge
telling something duplicate is entered.

So your idea worked for me. Thanks a lot.

 

[John W. Vinson]

I added a field called UniquenessChecking to the table and added it as an
unique index. On the data entry form, I added a text box with its control
source equal to all fields on the form concatenated. And in the beforeupdate
event, set the value in text string to the UniquenessChecking field. Then
whenever a duplicate record is entered, the system will give out a messge
telling something duplicate is entered.

Just be careful how you do the concatenation: it might give ambiguous or wrong
answers. E.g. in a first/middle/last name concatenation, "Robert", "A",
"Heinlein" and "Roberta" <Null> "Heinlein" give the same concatenated result,
falsely indicating that the record is a duplicate.

You can deal with this but you do need to do so!