Duplicate Search Query

[robertma]

Hi,

We have a 'Customer Details' form and on which we record basic details i.e. Name, Address, Tel number etc.

The problem we are experiencing is that customers are being contacted a number of times because users are too lazy to search the database and are creating new records every time contact is made with a customer, rather than search if any existing details are held. (Each time a new record is created a telephone call is made and letter sent).

Users have been warned countless times but we have been fruitless in success, what I have found for example is that if they are speaking to Mr Jones, they will search the name Jones find 213 records and create a new records rather than find the one applicable to him. (I know they could then again do a further search but this does not happen)

Ideally what I would like is that when a user clicks on the 'Create New Record' command button which takes them to a new record. A 'Pop Up' will appear with two field’s i.e. Customer name and postcode (These fields are ones used on the form).

The user can then enter these to quickly view any records that match the 2 fields.

Long winded I know but I’d be grateful for any help anyone can give, Please find attached the database used

Thanks

Matthew

[mushroom]

If you where to create a primary key using "Customer name" & "Phone #" no duplicates are allowed.

[M0rtym0use]

robertma,
the best way to do this is before creating the record check to see if a record already exists. If is does then display an error message else create a new record.

the way you would implement would obviously depend on the db type eg mssql, access, mysql,oracle etc.
and the programming language you would use to implement this

MM

[ronniethedodger]

That is what the Primary Key will do as Mushroom suggested. If you try to Insert the record, it will throw an error -- no need to check, the database engine does it for you.

In MySQL, you would get the following message:
Error: 1022 SQLSTATE: 23000 (ER_DUP_KEY) Message: Can't write; duplicate key in table '%s'


All you need to do is build proper error traps and then resolve the errors accordingly. For proper MySQL error handling, refer to http://dev.mysql.com/doc/mysql/en/Error-handling.html for more information.

This is also the preferred way of building database table structures. There will be cases that data could be loaded externally as from a text file. Letting the database engine do the checking for you and disallowing insertion of duplicate key values protects the integrity of the table.