I am a currently studying A-Level ICT and I am designing a system for a take-away restaurant which also provides a delivery system. I would like to give all users a customerID on a Customers table and decided that a good idea would be to combine their surname with their telephone number e.g. SMITH01234567890. This would ensure it was unique and also easy to remember. However it is prone to error for the person inputting the data and therefore I was wondering if there's a way of looking up and combining the surname and telephone number which are going to be taken anyway and inserting it into the CustomerID field? I don't think it's wise to remove the surname and telephone number fields from the Customer table altogether, although they do appear to be repeated redundant data, they are needed for the final reports (order receipts) and are related to other tables. The file with all the tables is attached (the design and structure is not finalised).

Regards

William Lau

Use an Autonumber for your CustomerID and Primary Key. Don't try the NamePhone number approach. It sounds like you will have repeat customers so have a Customer table and an Order table with the CustomerID being the Foreign key in the Order Table. (A One to Many relationship.) The Order table will have an autonumber as its primary key and you can use that as the number for that customer for that order as it will be different every time they call in a new order and you fill in their request.

hth,
Jack

That does work, but as i said it will be hard for customers to remember an autonumber that they are set. And also with the data protection act, the company is not allowed to hold records that haven't been used for a long time. Some records will need to be deleted and then the autonumbers will all shuffle up. This means the autonumbers for all customers below the deleted record will change. This is not really suitable as I need a quick and easy customerID that customers can remember, so that customer information does not have to be entered every time they order.

The auto number will not shuffle up. Are you suggesting that someone given a number SMITH01234567890 will remember it for a takeaway? Search initially by their address or Number and Postcode if you want that level of complexity

When dealing with the public you can never expect them to remember their customer number unless you are a bank and are holding their money hostage. Therefore although you will be using an autonumber as the pk to the table, your lookup should be based on a query that concatenates their last name with phone number.

Select CustomerID, LastName & HomePhone As CustTextID
From tblCustomer
Order by LastName, HomePhone;

The clerk taking the order will start typing into a combo that uses the above query as its rowsource. The list will scroll (this is the default behaviour) so hopefully the entire name will not have to be entered. When the name is found, the CustomerID will be stored in your order table. You'll need code in the notInList event to handle adding new customer records. You can find that in help or here.