Linking two tables - addresses to client enquiries

[gavlap]

I have a database currently and want to make some changes before upgrading it to MS Access 2010.

We are a holiday company, and currently everytime a client enquiries about a new holiday we generate a new record for that holiday enquiry. If its an existing client we have a form which can dupllicate a record creating a new record but duplicating the contact details (address, email, phone numbers etc).



This does work however you end up with duplicate contact data in each of the seperate records, so if the client has made 20 holiday enquiries their address is in each of them.

Aprart from the general issue with duplicate data such as slowing the system down the main problem arises when a customer changes their email, address or telephone number. Someone may go in a edit it in one record but then you end up with customer enquiries with various different addresses. Then it can even get more messy when an exisitng customer enquiries and we create a new record from one with their old address.

I know a better way would be to have addresses in one table, and enquiries in another but I just can't figure out how to link the tables. I thought of name but we have multiple clients with the same names (we have several Jane Smiths), I've thought of by email but clients change their emails and this would then break the link so I'm stumped.

Any ideas?? This is possible simple to sort but I'm not an advanced developer so just can't think of a way around it... appreciate any ideas!

[Rainlover]

Use AutoNumber as your Primary Key.

I do this for every table. It uniquely identifies each Record.

You will need a Join Table to Connect Enquiries to Name. A Many to Many Relationship.

[gavlap]

Thanks for that... I've read an article on 'Many to Many relationships' but I'm not sure if that's whats needed. I maybe wrong and have missunderstood it as I'm not that great at using MS Access but for each record we have in the [Enquiries_Table], there would only be one matching record in the [Contact_Details_Table]. So a One-to-Many Relationship should work shouldn't it?

The problem I'm having is finding something to link the two tables because a holiday enquirywould only relate to one client (eg. Jane Smith) but it couldn't be linked by Client_Name because we may have several different Jane Smith's.

Now thinking about using a Primary Key and somethings I think will work, but I'm not sure how I'd be able to use it to get around everything.

If an exisiting customer phones us I can think of a way of setting up a form, so that a user could bring up that client and create a new enquiry for that client. Which would then add the client_ID to the record linking the two tables.

However if a 'user' does't realise its an exisitng customer and creates a completely new record, it would creat a seperate cient_ID so you would then duplicate the client. So would you have to search everytime someone enquiries to see if they're in the database already before adding them?

Also we download around 20 enquiries a day from our website.. some of these maybe from exisitng customers. When we're adding these to the database would we have to manually check through each of them to see if they're on the database already, and if they are get the Client ID and at it to the enquiry? If I'm right and this is what you'd need to do, it seems quite a time consuming thing to have to do every day - is this right?

Thats why I thoguht of linking it by name but with us have different customers with the same name it doesn't work. I then thought of linking them by email_address but if a client has changed their email address it won't find the address.

Does this make sense as I rambling now and I think I'm confusing myself more.

[Rainlover]

So a One-to-Many Relationship should work shouldn't it?

Yes I think you are correct.

Autonumber in your Primary Table and Number in your
Foreign Table will work.
Avoiding Duplicates may not be possible. Maybe you could look at some form of Membership or Registration.