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
677 Views

Similar Articles

[PageSpeed] 37

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:

Summing values
Hi, I have several columns of data in the format "nnt" where n= numeric value, t=text. I need to sum up the numeric values, based on the text value. Is there any way I can do this? Thanks for the help. You need to give us more information, is it always 2 digits and one text character or can there be more than one text character? What would the condition be. For a test assume there is always one text character and always at the end 12t 1n 24t 9t 16n assume you want to sum all the numeric values that ends with the text character t =SUMPRODUCT(--(RIGHT(A1:A5)="t"...

Adding Notes Field to a Phone Call Report
We are wanting to put together a report that lists phone calls by completed. When the report runs, we would like to have any Notes that are attached to the phone call show up in the report. Anyone know how to accomplish this? Thanks!! Cammie On Mar 25, 3:24=A0pm, Camilla Turner <CamillaTur...@discussions.microsoft.com> wrote: > We are wanting to put together a report that lists phone calls by complete= d. =A0 > When the report runs, we would like to have any Notes that are attached to= > the phone call show up in the report. =A0 > > Anyone know how to accomplish ...

Why does rpc over https not work?
Hi all, When I configure the rpc over https on the exchange 2003 servers, I still can not make a connection as it pops me for the password. After I entered my domain password, it can not be accepted. We have FE and BE servers and exchange 2003 SP2. I configure s tje FE server to use basic authentication and integrated authentication on the backend serves. I can not figure out why it does not work. Can anyone help me with these? Hi, Try to access https://common-name/rpc. It should prompt you for user name and password. If it is giving the certificate warning, install the certific...

OUTLOOK 97 Resetting time span for email arrival
I am trying to avoid going into TOOLS "check for new mail" and have OUTLOOK continuously display new emails. What can I do? I did not see anything under "Options" that gives me a choice for resetting. ...

Money 2006 Portfolio two different values for Annualized Return
I have the test drive version of Money 2006. In the portfolio manager, I have the Annualize Return column visible. This provides values for each holding, each account, and the entire portfolio. At the bottom of the portfolio manager, I have selected Annualized Return as one of the three to display. My totals line my portfolio displays an annualized return of 7.5%, on the bottom, it displays 10%. I bounced around changing the bottom line to display values such Cost Basis, Market Value, and Total Returns and these all match the line labeled as Total Portfolio Value ($). I believe t...

WSDL generates with wrong "minOccurs" values
Using the XSD.exe tool, I created a number of classes from my XSD file. When I generate the WSDL for my web service, the schema does not have the proper Occurance constraints for the attributes. In this example, the MessageID element should always occur (minOccur=1, maxOccur=1), but the WSDL generates minOccur=0. I can't pinpoint why this is happening and I can't find a may to force this in the code either. Anyone have any insight? This is happening with a large number of elements, I just provide one example below. Code of the schema, classes, and WSDL excerpts below. Thanks. //...

Calculating diff between date/ time only between certain hours 02-24-10
I am trying to calculate the difference in hours between a date/time range but only the hours in a certain range. 1/27/10 4:49 1/27/10 11:32 I am trying to find the diff in hours only between 6:00 and 23:59, any hours that fall outside of that time frame should not be counted. Each calculation will have different dates so if there is a way to do this without specifying the date that would be great! GMv1 - If your start date is in A4 and your end date is in B4, then you can do this: =MOD(B4,1)-IF(MOD(A4,1)<0.25,0.25,MOD(A4,1)) This works becase date/times are stor...

How do I format the Auto Date font that is inserted
Sorry to bother with such a minor thing.... When creating a page that is captured from the web, a date and time is already inserted at the top of the page... which is great. The only problem is that it is so tiny and light colored font that it is hard to see. Is there a way to change that font? Thank you. Nancy PS- I just started using Onenote and it is a great program! I just love it. Perfect for what I need. Never mind... I see the answer in a previous post. Sorry, I thought I had read all the previous post headers. Nancy "Nancy" <weewee37@hotmail.c...

how I can insert date picker in excell template?
Maybe one of these will help: http://www.rondebruin.nl/calendar.htm http://www.fontstuff.com/vba/vbatut07.htm Regards Rowan ...

Color rows based on date in a cell?
Programming question? Hopefully so. Excel novice here I would like to color a row yellow at a certain date, then red at another. This is for a project and the yellow and red correspond to due dates. Say at 12/11/2009 the row is yellow. At 12/19/2009 it is red. I would also like to color a row (some color) when a 100% is reached in the "completed %) cell. Help is appreciated! Ignore, as this is now in General Excel forum,, "tbo" wrote: > Programming question? Hopefully so. > Excel novice here > > I would like to color a row yellow at a certai...

multi thread required for async sockets?
I am going to have a client app that will connect to multiple servers for data. Data will come every second, if I am not connected the data will get lost. Thus I need to keep the socket connected at all times. Can I just use one thread with an array of async sockets without fear of data going into the wrong place? You will have to derive a class from CAsycSocket so that you can receive the data (you receive the data by calling Receive within the OnReceive method). What you do with the data has nothing to do with the number of threads in your program. AliR. "amccombs" <...

Need help (Average excluding zero values)
This is what i have & it works but one problem there will be zero values & i need to exclude them if possible =AVERAGE(IF(AA5="L",E$5:E$65)) I have tried several different combinations I can,t get it that includes zero values in the average for me. are they blanks and not zeroes? -- Gary Keramidas Excel 2003 "Mike" <Mike@discussions.microsoft.com> wrote in message news:29096CBB-3E2A-4828-8A49-6CEB0142D5C4@microsoft.com... > This is what i have & it works but one problem there will be zero values & i > need to exclude them...

SendObject silent dos not work any more
I have a routine that creates a list of e-mails to be sent out, when the list is completed it then loops through and uses the Docmd.sendObject to send each mail (Access 2000). This used to work very well and I have not had any problem with it until about 3 months ago when it started to send the first mail all ok, out look would ask if it was ok to send and you said yes and it was sent and so on, but now it sends the first mail all ok and then it just loops through the rest with no outlook messages or error messages but dos not send any of the other mails. This used to work very well abo...

possible to change field code color?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel It's currently set to medium/dark gray and I'd like to change it to something less visually jarring... light gray would even do it. <br><br>Of course I know that the field codes will not print / they're only for on-screen use... but 99% of the time, I work with my docs on screen. <br><br>Thanks! I don't know of any way to change the color of the Field Shading, but in Word> Preferences> View you can select an option to either not have them Shaded or to have them S...

Displaying multi-select parameter on report?
Hi, I have a report where I use a multi-select parameter to filter the report by the user's initials who entered the record. I typically like to print the paramters selected for a report in the header of the report. However, when I put this parameter.value on the report I only get the first option selected. How can I list all the selected entries? SQL Server 2005. Thanks in advance, Linn Linn, To take care of this you can use the Join Function to deplay a delimited list. Here is an example: =Join(Parameters!ReportParameter1.Value,",") The sec...

How do Count a the number of times a date range appears within a date range!?
I'm stumped, I have no real idea on how to do this so any help much appreciated! I have a start date (no year ie, 1st December) and an end date (again no year say 5th Jan), And a date range, eg 11th May 2001 to 1 January 2006 for info late: 1st Dec to 5th Jan = 35 days How do i get a count of the number of 1st Decemebers to 5th Januarys in that date range for example 1st December 2001 to 5th January 2002 is 1st 1st December 2002 to 5th January 2003 is 2nd 1st December 2003 to 5th January 2004 is 3rd 1st December 2004 to 5th January 2005 is 4th 1st December 2005 to 1th January 2006 is...

How to change transaction date?
I'm using Money 2003 Deluxe and Business. I use online bill payment through my bank. There is a recurring rental payment each month. It is processed on the 20th for a due payment on the 25th. Money will see it and enter it into my Money account as an electronically cleared item. Sometimes the item will clear so that I have two rent payments occuring in the same month. This skews my expense report because the next month has no rental payment. I'd like to see one rent payment each month for budgeting purposes. Right now, I'm subtracting one of the rental payments manually to g...

Time Convert
Hello group, I need help converting time to a general number example 00:00:07, I need to convert to just plain 7 another example 00:01:07 would be 67 fyi im exporting from avaya IQ Thank you Jaime Hi Jaime, Am Mon, 5 Jul 2010 09:24:32 -0700 (PDT) schrieb 818jaime: > I need help converting time to a general number example 00:00:07, I > need to convert to just plain 7 another example 00:01:07 would be 67 > fyi im exporting from avaya IQ your time in A1, then in B1: =A1*86400 and format for B1 "general" Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office ...

Outlook Connector, how I can setup the sync time?
Hello, the Connect works very well in general, but it works a little bit too much. It sync eache ONE Minute. I have tried: 1) ALT+CTRL+S, create a new group and setup the time to 30 minutes 2) http://support.microsoft.com/default.aspx/kb/889694/ but no luck. Peter Outlook Connector 12.0.6407.1000 Outlook 2007 12.0.6316.5000 SP1 MSO 12.0.6320.5000 Vista ultimate 32 with SP1 and all updates How many accounts? Try a new profile with just one acct and see if it syncs less frequently. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Soluti...

Date and Time Table Modified
I have 4 forms used by managers to enter data. When they enter data, the data is saved in tables. I would like to include a date and time stamp so that I know when they entered each piece of new data. Do I need to have a field in my table to collect the date and time? How do I collect that in my form? Thanks!! Yes, you need to add a field to your table. In the form's BeforeUpdate event, put code to update that field. Assuming that you named the field LastUpdated and that you included the field in the form's RecordSource, the code would look something like: Private Sub Form_Bef...

SendObject doesn't find fields in querry
I have a form that has a button to run an "event Procedure" to preview a report, and to email the report as a pdf attachment. The codes I use are as follows: Private Sub emailfullpricesheet_Click() DoCmd.OpenReport "Full Price Sheet", acViewPreview, , , acWindowNormal DoCmd.SendObject acSendReport, "Full Price Sheet", acFormatPDF, , , , [DLRName], _ "Thank You for your business.", True End Sub In this code, I have the field for the dealer name (shown above as [DLRName]) where it would be placed in the subje...

Multiple Outlook Profiles Open at One Time
Hi, I pull mail from an Exchange server (through POP) - to keep it on my own box. However, I need to save most items - thus I run into the large .pst file. I have thought about setting up a new profile every three to six months - but I always need to access my older mail. Is there a way to open more than one instance of Outlook - to different profiles at one time? Or does anyone have any other suggestions. I've tried other files - on the outlook bar - however they are not configured like email messages. Thanks, Patty Multiple profiles would be overkill here. All you need ...

formula to subtract time, 12:10 am (00:10)
Hi One way With 00:10 in A1 and 23:50 in B1 =MOD(A1-B1,1) -- Regards Roger Govier "teezee" <teezee@discussions.microsoft.com> wrote in message news:BAAF0132-424F-4491-B9E8-0ABFB9B4D0CB@microsoft.com... > ...

number of fields exceeds 256 when importing from a dbf file
Hey evryone, I'm having a small problem with importing some old data from a FileMaker Pro database. According to what I found out in the groups archive the best bet is to convert it to a dbf file and then import it. However, I the number of fields in the dbf file exceeds 256, so the import wizard truncates the import at field number 256. I would now want to spread the data over two sheets. However, in the process of converting it to a dbf, quite a few of the field names get truncated and therefore have the same name, so I can't use SQL to retrieve them. My question is: what i...

time stamp upon closed
Hi Everyone, I am trying to have a time stamp mark my date_completed field when an operator changes the status field to closed. Closed is in my tblstatus table along with submitted, pending, postponed, ect. I am guessing that the code would be in the afterupdate event of the status field on my form but not sure? Iwould rather the system date the field rather than an operator. Any suggestions? I would suggest that you use a combo box for the status field. That way, the correct codes will always be entered. Then to update the date_completed field in your table, put a text box on y...