Data Duplication Check

Hi, very new to access!! I am attempting to develop a database for my brother. I want to add some form of validation to prevent the same information from being input more than once in the customer table. I thought a good way to do this was after a new customer record has been input (& all fields completed) when save option is selected the 'Phone Number' (required field)could be checked to make sure it doesn't alreadt exist. i.e. AfterUpdate Event do............ Any ideas on the code??

Also if I use 'Number' as data type for CustomerID (as opposed AutoNumber) how can I ensure after each customer entry the CustomerID is incremental of the last entry? I thought maybe I could have a table 'Count' which kept track of the numbers and assigned next number to new entries (not sure how to implement this however).

All help greatly appreciated.
Phil.

Email: URL

If you are never going to have the phone number duplicated (ever) then in the design view of you table, change the Index on the Phone Number to "Indexed(No Duplicates)" the the database will not allow that entery into the system. For that matter, If you will not allow Phone Numbers to be duplicate, then make it the PrimaryKey.

S-

That makes sense!! Another question. I have PK in customer table set to AddressID. I have been told AutoNumber is not a good idea for data type as this can lead to problems when DB's require replicating. If i set this data type to number when a user enters a new customer record it will be refused as all AddressID need to be unique & are 0 by default (stay wih me!!). I thought I may be able to create a count table that counts the value for the last entry then automatically increments AddressID for future entries? i.e.
Table: Count
TableKey Table Count
AddressID Customer ??????

I have no idea how to implement this (get it all working). Do you think this sounds feasible.

Thanks for your time.
Phil.

So if I am understanding you correctly, you don't want to use autonumber because you have been told that it increases the chance of duplicate entries. So want you want to do is manually auto number your records, using another table hold the next available ID.

Either way you are still autonumbering. Now if you concren is to control the next number used (which you can't with AutoNumber). The create a table where you store the next available Number to be used. When you add a record, go to that table, grab the number, use it , add 1 to it, put the new number back for the next time you add a new record.

As far as duplicate entries are concerned. I know of only a few ways to outsmart a users and not allow duplicate entries, but those are focused around SSN#, Credit Card Account Numbers, ETC. Outside of that, users always seem to find a way to screw up. What you want to do is make it really eay for users to find exisiting customer, even use searchs based on the sound of the customer Name(soundex), so they don't have a need to enter data twice.

Good Luck

S-

When you put it like that..........I think I need to go back to the drawing board. Reason. I have the Customer table & another table Orders. I need to link the two. In the Customer table i have AddressID set as Autonumber. In Order table I have OrderID set as Autonumber. I have AddressID as a foreign key in in Orders. I can not set this as AutoNumber as Access only allows one field to be autonumber. If I set AddressID (FK) to number I get an error when enforcing referential integrity.....blah......blah...... Any suggestions on best way to set up table structure to hold data about Customers & Customer contacs & also Orders and Order details + relationships data types etc........
Help much appreciated. If I can get the shell, see how it all works I can then build on it!! That's the theory anyhow.
Cheers,
Phil.

I don't know if I completly understood you, but...

In Customer

AddressID -->AutoNumber -->PK

Order Table

OrderID -->AuntoNumber -->PK
AddressID --> Number --> FK

Is Correct

S-

Thanks for keeping patience!! Yep that about sums it up. I would attach what I have done but the file is too big for this forum! I have now changed AddressID to CustomerID!! I appreciate your time but I think this will run and run as I am quite dense!! I think I will get a GOOD book. How do I delete a thread? If you feel compelled to see what mess I have created email me at: URL and I will send you the offending article. If not, once again many thanks.......
Phil.