Problem with duplicates

[hawkesms]

Hi!

I have a table where I need to check for specific duplicates when the user enters in some information.

I looked at just using 'DISTINCT' with 'No Duplicates' but this won't work as I cannot get away from having some duplication

i.e. the column in my table has this (and this represent the format of the data too)



ABCDEF (this is OK)
ABCDEF (this is OK)
ABC 0123 (this is NOT OK)
ABC 0123 (this is NOT OK)

Can anyone help???

Many thanks!

 

[SammyJ]

Why is one of those not OK, but the other is? Do you want the data to be rejected from the table when the addition attempt is made? Where did you use DISTINCT? There isn't mention of a query here.

 

[hawkesms]

Hi SammyJ

Thanks for replying.

This is basically a quotation system so we can enter VERBAL for a verbal quote but if a 'proper' quote is made a code is given to it eg ABC 0123
Therefore, we can have many 'VERBAL' quotes but we don't want to duplicate the codes.

Unfortunately it's not possible to have these seperate as I would like them to be.

Sorry about the DISTINCT bit I got a bit confused.

What I was referring to was going into the design view of the table and selecting the Data Type of the Field Name and then setting the Indexed to Yes (No duplicates)

Many thanks!

 

[SMatthews]

Can you have two tables, one for Verbal quotes and one for Proper quotes? If not, you could make another field in the table you have and give every entry a unique identifier. You could then store "verbal" or "proper" in a separate field.

 

[Bat17]

I think that you will need to do this at form level when the data is entered.

Peter

 

[SamDeMan]

hi

as bat17 wrote, the important thing to remember is that validation is at the entry point. one of the biggest mistakes i made in the begining is that validation is achieved on table settings. now i find that the only restrictions i have on tables is type. i don't use any more lists on tables or no replication. i do everything on the form. i think you will find that one of the most complex areas in access is developing forms.

good luck,

sam