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

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?

0
rn5a
5/10/2007 3:16:59 AM
access 16762 articles. 3 followers. Follow

4 Replies
721 Views

Similar Articles

[PageSpeed] 2

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>




<rn5a@rediffmail.com> wrote in message 
news:1178767019.921138.3440@n59g2000hsh.googlegroups.com...
>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?
> 


0
Ken
5/10/2007 4:07:39 AM
On May 10, 9:07 am, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> 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>
>
> <r...@rediffmail.com> wrote in message
>
> news:1178767019.921138.3440@n59g2000hsh.googlegroups.com...
>
>
>
> >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?- Hide quoted text -
>
> - 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

0
rn5a
5/10/2007 7:02:54 AM
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>



<rn5a@rediffmail.com> wrote in message 
news:1178780574.853259.184620@o5g2000hsb.googlegroups.com...
> On May 10, 9:07 am, "Ken Snell \(MVP\)"
> <kthsneisll...@ncoomcastt.renaetl> wrote:
>> 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>
>>
>> <r...@rediffmail.com> wrote in message
>>
>> news:1178767019.921138.3440@n59g2000hsh.googlegroups.com...
>>
>>
>>
>> >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?- Hide quoted text -
>>
>> - 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
> 


0
Ken
5/10/2007 11:26:05 AM
On May 10, 4:26 pm, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> 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>
>
> <r...@rediffmail.com> wrote in message
>
> news:1178780574.853259.184620@o5g2000hsb.googlegroups.com...
>
>
>
> > On May 10, 9:07 am, "Ken Snell \(MVP\)"
> > <kthsneisll...@ncoomcastt.renaetl> wrote:
> >> 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>
>
> >> <r...@rediffmail.com> wrote in message
>
> >>news:1178767019.921138.3440@n59g2000hsh.googlegroups.com...
>
> >> >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?- Hide quoted text -
>
> >> - 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- Hide quoted text -
>
> - 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

0
rn5a
5/11/2007 2:25:39 PM
Reply:

Similar Artilces: