Duplication problem

[torquay]

Thank you for taking the time to look my question. Hopefully I can be pointed in the correct direction.

I am trying to stop duplicates in my customer table, the table is as follows

CustomerID PK
Salutation
Surname
Address1
Address2
Address3
City
PostCode
Phone
Cell

I have the surname, address1, City & Phone number Indexed(Duplicates ok)
but if the combination of all 4 fields match then I want this to be not allowed.
I guess maybe I should make the primary key the combination of all 4 fields, not sure how to do that though.
Hope this makes sense.

Thanks

 

[DCrake]

Simple Software Solutions

Doing that would make life complicated.

When a user attempts to add a new customer the code your app to check for duplicates prior to commiting the add new customer code. Not knowing how you are doing this then I cannot comment further. Other than try

X = Nz(Dlookup(PrimaryKey,Table,"[CustmerName]='" & CustName & "' And [CustomerPhone]='" & CustPhone & "' And ....."),0)

If X > 0 Then
   ....Exists
Else
   .... New
End If

Code truncated for brevity

CodeMaster:

 

[Dennisk]

Personally I wouldn't bother. Supply the user with a combo box of all the clients sorted into surname order. So the ownus is on the user to determine if the client exists or not.

The reason I don't bother checking like this is

1) spelling mistakes, Smyth for Smith
2) Abreviations, Roud, rd. for road
3) Spaces in Address, flat 5, 53 or flat 5 53
4) Spaces in telephone numbers 999 8888 or 999888

None of the above will produce a match