Does Multi-Field Index Work For Date/Time Values Only

[rn5a]

I created a MS-Access DB table with the following 3 columns:

ColID - PRIMARY KEY
Col1 - Number
Col2 - Date/Time

Next I created a multi-field index using Col1 & Col2. I entered the
following row in the 1st row:

1 5/10/2007

Next when I tried to add the above row again, as expected, I wasn't
allowed to enter the same row. Next I entered the following 2 rows:

1 5/11/2007
2 5/10/2007

Both the rows were accepted. After this I deleted the 3 records, went
back to the design view & changed the data type of Col2 from Date/Time
to Number keeping the multi-field index as it is.

After this I entered the following row in the table which is the 1st
row (in Col1, Col2 order):

1 1

Now when I again entered the above row, Access accepted it but when
the data type of Col2 was Date/time, Access didn't allow me to enter a
row whose column values already existed in the table.

Does preventing duplicates using indexes work only with Date/Time data
type?

 

[Ken Snell \(MVP\)]

An index doesn't mean that no duplicate data can be allowed. That depends
upon whether you set the Unique property to Yes for the index -- regardless
of how many fields are in the index. Open the index window (icon with
lightning bolt on it) and click in box that has the name of the multi-field
index. Look at Unique property at bottom of window.

 

[rn5a]

An index doesn't mean that no duplicate data can be allowed. That depends
upon whether you set the Unique property to Yes for the index -- regardless
of how many fields are in the index. Open the index window (icon with
lightning bolt on it) and click in box that has the name of the multi-field
index. Look at Unique property at bottom of window.

--

Ken Snell
<MS ACCESS MVP>
















- Show quoted text -

Thanks Ken for your suggestion. I have made the index UNIQUE in the
same way as you have suggested but, unlike Date/Time data type, it
doesn't work with the Number data type (but SQL Server supports Number
uniqueness).

Thanks once again,

Regards,

RON

 

[Ken Snell \(MVP\)]

It would be very strange if the index was allowing duplicated values when
the Unique property is set to Yes. There is no difference in how the index
handles such situations just based on the data type of the fields involved
in the index. I would have to guess that the index is not set up quite the
way you wish and that that is why you're getting strange results.

When you view the index window, a multi-field index is set up by having a
name in column 1 for the index name (in the first row of the rows that will
describe the index), and the first field of the multi-field index in column
2 in that row. Then the next row should be blank in column 1 and the name of
the next field in the index in column 2. Is this how you have the index set
up?

 

[rn5a]

It would be very strange if the index was allowing duplicated values when
the Unique property is set to Yes. There is no difference in how the index
handles such situations just based on the data type of the fields involved
in the index. I would have to guess that the index is not set up quite the
way you wish and that that is why you're getting strange results.

When you view the index window, a multi-field index is set up by having a
name in column 1 for the index name (in the first row of the rows that will
describe the index), and the first field of the multi-field index in column
2 in that row. Then the next row should be blank in column 1 and the name of
the next field in the index in column 2. Is this how you have the index set
up?

--

Ken Snell
<MS ACCESS MVP>








- Show quoted text -

Your are right....Ken...I must have been doing something wrong
yesterday while creating the index because today when I tried it, it's
working fine!

Sorry for the uncalled post.

Thanks,

Regards,

RON