Question No duplicates ?

[-Chris-]

Hi all, how do you do it, when for example, in a field, you don't want any duplicates, but in the same field you may have 2 values. So for example, in the email field, in one record you have test2 at emailaddress then in another record (still in the email field), you have test1 at emailaddress and as soon as you input for example test2 at emailaddress it tells you you can't do that, as it would create a duplicate. How would you solve that one please?

 

[Frothingslosh]

I think you may want to re-phrase the question.

What I'm getting from reading it is that you don't want duplicates, but you don't want it to reject a duplicate entry, which is rather illogical.

If you index a field and set 'allow duplicates' to NO, then it's never going to allow you to enter a duplicate value, period.

 

[-Chris-]

Afraid it's exactly as stated. To flag up a duplicate of any part of the field, with a duplicate from any part of the same field from another record

 

[Frothingslosh]

That's not how unique indexes work. When you index a field and set it to No Duplicates, the ONLY thing it checks is the value of the entire field.

Access WILL NOT block you from entering a partial match, so what you're saying is happening is not something that can actually happen.

So let's start over. Explain precisely what is going on, and do not assume we know anything about this table. If you're using lookup fields, say so. If you're using a never-to-be-sufficiently-damned multivalued field, say so.

If Access is calling partial matches 'duplicates', then you have a bigger problem, because that means that it's reading, say, 'Apple' and 'App' as identical strings.

 

[jdraw]

I agree with Froth there is a certain lack of clarity in your post/need.
Perhaps you could show readers an example.

Duplicates in a field in Access(database) means character for character for the full field length.
If you are trying to put 2 values into a field called "email"

eg. email ="myemail@gmail.com;emailjimX@mynet.com"
then you would have to have a custom extract and parse to separate
myemail@gmail.com and
emailjimX@mynet.com

and as Froth said MVF -multi-valued fields are a poor storage/design strategy - if that is the root of your issue.

 

[The_Doc_Man]

Let me see if I got what you are trying to do.

Are you trying to say that you have TWO potential e-mail fields and you don't want someone to enter the same address to both fields? That's the closest I could get to making any sense whatever out of your question. If that's NOT what you meant then you need to rework the question.

 

[isladogs]

Afraid it's exactly as stated. To flag up a duplicate of any part of the field, with a duplicate from any part of the same field from another record

To take this to its logical endpoint, the '@' symbol will be contained in the email address field for every record.
Therefore if I understand what you are saying, every record will contain a duplicate of part of every other record so all will be flagged

That seems rather ridiculous !!!!
None of us can really make any sense of what you want without specific examples

 

[jdraw]

cross posted
http://www.accessforums.net/showthread.php?t=66455

-Chris-
When you cross post --post same/similar question in multiple forums, please identify those other posts.
Cross posting is fine, if done with some notification to readers.

Here's why.