If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
=?Utf-8?B?Q1ZM?= wrote in news:A55A52DF-
: but i require 10 max to be processed at one time, This doesn't mean you have have ten fields side-by-side in the table does it? Tim F |
#12
|
|||
|
|||
Yes there are 10 fields for this data.
I understand that it will not pull up a blank record as a duplicate when Ignore Nulls is set to No. However this is still not working with ANY data, if i enter in the same data in 2 fields or over 2 records it does not recognise it as a duplicate. |
#13
|
|||
|
|||
If I understand your previous posts properly Craig what you are saying is
that a NULL is not a 0 or a blank entry but an unknown value. Therefore it reads every object in the field as unknown and it cannot record the value of something that is not known in the index. I've now set the option to Ignore Nulls No. This means it should now record the entries and because I have selected Unique to Yes it should recognise duplications. However it is not. It appears to be only working on a single index. I have a 2 field multiple index working on a trial database which I wrote several weeks in advance and it is working correctly, however if I now make a new database of any sort it does not do the same, I have checked the options against one another and they are identical. Also as well as that I have tried to create the same thing on a different computer under Access 2003 and have a simlar problem, however on that machine it reads it as a single index rather than ignoring the index completely as it is at the moment. |
#14
|
|||
|
|||
No, it is not that Jet can not record the value of something that is unknown
(i.e. Null) in an index. It can do that quite happily. The point is that generally, there is little point in doing so. The Jet database engine treats Null as an unknown value. That means that a comparison of two Null values does not evaluate to True. Is one unknown value the same as another unknown value? The answer is neither True nor False, but unknown, i.e. Null. So I can have a unique index on the combination of Field1 and Field2 and I can happily add multiple records all with Null values in those fields. As far as Jet is concerned, these are not duplicates, because Null = Null does not evaluate to True. What this means in practise is that in a Jet (MDB) database it does not usually make sense to allow Null values in any field that forms part of a unique index. With some possible rare exceptions that I can't think of right now, if you want a unique index, you will want to disallow Null values in any of the fields included in that index. -- Brendan Reynolds (MVP) http://brenreyn.blogspot.com The spammers and script-kiddies have succeeded in making it impossible for me to use a real e-mail address in public newsgroups. E-mail replies to this post will be deleted without being read. Any e-mail claiming to be from brenreyn at indigo dot ie that is not digitally signed by me with a GlobalSign digital certificate is a forgery and should be deleted without being read. Follow-up questions should in general be posted to the newsgroup, but if you have a good reason to send me e-mail, you'll find a useable e-mail address at the URL above. "CVL" wrote in message ... If I understand your previous posts properly Craig what you are saying is that a NULL is not a 0 or a blank entry but an unknown value. Therefore it reads every object in the field as unknown and it cannot record the value of something that is not known in the index. I've now set the option to Ignore Nulls No. This means it should now record the entries and because I have selected Unique to Yes it should recognise duplications. However it is not. It appears to be only working on a single index. I have a 2 field multiple index working on a trial database which I wrote several weeks in advance and it is working correctly, however if I now make a new database of any sort it does not do the same, I have checked the options against one another and they are identical. Also as well as that I have tried to create the same thing on a different computer under Access 2003 and have a simlar problem, however on that machine it reads it as a single index rather than ignoring the index completely as it is at the moment. |
#15
|
|||
|
|||
The following entries are not duplicates
"Test", Null, Null, Null, Null, Null, Null, Null, Null, Null and "Test", Null, Null, Null, Null, Null, Null, Null, Null, Null If you set Unique to True and Ignore Nulls to True Neither of the above records will be added to the index, they will be recorded in the table. If you set Unique to True and Ignore Nulls to False Both records will be added to the index and they will be recorded in the table. It does not matter what you do with the Ignore Nulls setting as far as the records in the table are concerned if any field contains a null. You do not see the index you see the records in the table. Consider using default values for each field and understand what these default values represent. Note: If you use SQL Server you should note that it "incorrectly" evaluates a Null as equal to Null, this would allow you to achieve what you seem to want however nulls are BAD and very very BAD in unique indexes. I am not aware of any difference in the handling of this matter in Access 97, 2002 or 2003, Jet is more correct on this matter than SQL Server. -- Slainte Craig Alexander Morrison "CVL" wrote in message ... If I understand your previous posts properly Craig what you are saying is that a NULL is not a 0 or a blank entry but an unknown value. Therefore it reads every object in the field as unknown and it cannot record the value of something that is not known in the index. I've now set the option to Ignore Nulls No. This means it should now record the entries and because I have selected Unique to Yes it should recognise duplications. However it is not. It appears to be only working on a single index. I have a 2 field multiple index working on a trial database which I wrote several weeks in advance and it is working correctly, however if I now make a new database of any sort it does not do the same, I have checked the options against one another and they are identical. Also as well as that I have tried to create the same thing on a different computer under Access 2003 and have a simlar problem, however on that machine it reads it as a single index rather than ignoring the index completely as it is at the moment. |
#16
|
|||
|
|||
|
#17
|
|||
|
|||
I can see where your coming from, however this would require the user to
enter in the data several times for one entry and also the information does not need to be used anywhere else nor is it linked to anything else. What I'm trying to achieve is saving the values of these 10 fields to one place where duplications cannot occur. If I used a foreign key for this I would have to run two forms 10 times each, making it even more complicated than using a single index where I would only need to run one 10 times. Unfortunately for processing reasons one number per entry is not a workable solution. If there is another way to achieve the same thing by not using indexes possibly by some coding placed upon a button which says put simply if field 1 = field 2 display a messagebox to say Cannot process a duplication. The problem with the coding is that I can see no way to search all the previous records as well as the existing one. |
#18
|
|||
|
|||
Ok. So your saying the blank entries is the problem.
Why then when I create a new database and have 2 fields only plus a primary key, multiple index the 2 fields and enter in details for both the fields on every record does it still record say: "Test, Test" and "Test, Test". Is there prehaps an option in Access or an extra item I require to install to run this properly? Is there another way of ensuring the same value does not get entered into the 10 fields throughout the database? eg "Test" cannot be placed on another field on the record OR on another field in ANY record. |
#19
|
|||
|
|||
=?Utf-8?B?Q1ZM?= wrote in
: If I used a foreign key for this I would have to run two forms 10 times each, making it even more complicated than using a single index where I would only need to run one 10 times. No, of course it wouldn't. You can make the UI do whatever you want, and the recommended practice is always to echo (or improve upon) the users' favoured working practice. Your last comment in the original thread: eg "Test" cannot be placed on another field on the record OR on another field in ANY record. really tells me that the whole design is badly wrong. Best wishes Tim F |
#20
|
|||
|
|||
On Wed, 1 Sep 2004 15:07:02 -0700, CVL
wrote: Is there prehaps an option in Access or an extra item I require to install to run this properly? You can create a unique Index on any field *OR* on any combination of up to ten fields, ensuring that no two records can have the same combination of values. For instance if you had three fields A, B, and C with a unique Index, you could add records: A B C 1 2 3 1 2 4 1 3 4 1 3 3 because, though any field has duplicates, no two records have the same set of values. Attempting to add another record A B C 1 2 4 would fail with an index error. To create such an index, open your table in design view and select the Indexes icon (like a lightning bolt hitting a datasheet). Name the index in the left column, and select one to ten fields in sequential rows in the right. Specify that the index is unique. John W. Vinson[MVP] (no longer chatting for now) |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Merging with Access queries - Multiple | CPutnam | Mailmerge | 5 | August 21st, 2004 01:11 PM |
Multiple Many-To-Many Tables | Tom | Database Design | 7 | May 15th, 2004 03:47 AM |
Opening multiple workbooks in multiple windows | Dave Peterson | Worksheet Functions | 0 | February 22nd, 2004 04:20 PM |
Multiple Conditions and Multiple Solutions | Frank Kabel | Worksheet Functions | 0 | February 4th, 2004 09:22 PM |