Indexing question

[Helystra]

When using indexes is it ok to index a field by itself (and allow duplicates) and then use it as part of a multi field index which doesn't allow duplicates.



For example. I have Employee Number, First Name and Last name fields in a table. If I index them separately allowing duplicates in the name fields but then index all three fields together so that I don't end up with more than one entry for the same employee is that OK or do I even need to index the fields individually at that point?

[June7]

This is for your Employees table? Actually sounds like good idea.

I have never even looked at setting up multi-field index. Interesting. I have one table where this could be useful.

[Helystra]

I found it in Roger's Access blog. Seemed like it would be useful for keeping away duplicate entries. I just wasn't sure if doing a single field and then using that same field in a multi field index was a good idea or a no no.

[June7]

I don't think it will hurt. Although there is such a thing as overdoing indexing. Indexing on text fields is supposed to be slower than number fields.

http://office.microsoft.com/en-us/ac...010210347.aspx

[Helystra]

I will give it a go and see. If it's too slow then I'll switch it up.

[Dal Jeanis]

It's legal, but I'm not sure why you want a multifield index for that application. You shouldn't prevent the database from having two Jose Martinezes or two Jane Greens or two Tran Nguyens, so you need the Employee ID to make it unique, and if you already know the Employee ID number, then the name is redundant as far as lookup keys are concerned. So, what are you trying to achieve by the multifield key?

[Helystra]

I'm actually going to use it on the assignment table. I used employees as the example because it was easier. But if I apply the multi field index to the school job and the employee, I won't end up with duplicate entries assigning the same employee to the same job twice.

[Dal Jeanis]

Ah, now THAT is a good usage. Very appropriate.

[Helystra]

Thanks. Remember, I was concerned about that problem and I think this will fix it.