Append Query Confirm/Reject Certain Records

[djackson]

Ok, i need some big advice from you people i think.

Let me explain:

I have an append query that adds records to my main customer table from a temporary table. The temporary table contains the up-to-date customer info exported from my sales system. The records are appended if the customer number does not already exist in the main table. This is not a problem.
But, as part of my database, call records are logged and can be assigned to either an existing customer, or a prospective customer which will not already have a customer number.
Therefore, i have set my database up so that it gives a prospective customer a temporary number (autonumber) in the main customer table.
Calls can then be logged against this number, and then the number can be changed to the one from the sales system once they become an active customer.
Again, this is fine.

My problem is, i would like something in place when i run the append query that checks to see if the new customers that are been appended are not already in under a temporary number, and then giving the option to change the temp number to the live one.
The check could be based on postcode and/or company name, as these are likely to be the same, but i would also need an option for if the customer is not already in.
I have no idea at all how to implement this, so any help to set me on the right track would be greatly appreciated.

I hope some of this made sense.

Cheers

Dave

 

[djackson]

Can anybody help me with this please?
Any suggestion or point in the right direction would be much appreciated.

Thank you

 

[neileg]

Not an easy task to automate. The easy part is to create a query that joins you temp table to the main table using a join on both company name and post code. This will return results where there is a duplicate.

But its the variability of the data that will trip you up. You know that Blue Water Limited, Bluewater Limited, Bluewater LTD, and Bluewater ltd are probably all the same company, but Access doesn't. Nor does it see the match between EC1V 200 and EC1V 2OO. How are you going to deal with that?

 

[djackson]

That is part of my problem
The only workaround i can think of (but don't know how to implement) is to have an alert if either the postcode OR company name already exist, and if there is more than one instance of the same postcode, which is very likely, show them all and have the option to choose.

I realise this still is unlikely to capture every existing case, due to the many different ways people can enter the data, but it will catch the majority of them. The rest can be manually picked up later on.

 

[neileg]

I suggest you have a look at the Find Duplicates query wizard and see how far that takes you.

You might also look at using a combo box to capture the company name when calls are logged. One of the standard features of a combo box is that it 'zooms' to existing values as you type and can be used to help user identify existing names.

 

[DCrake]

Don't know what type of sales database you have and what customisation rights you have but one workaround would be to use a spare field in your prospects data table and enter the temp number you have given it in your temp table in Access then when you perform the update you can use this to identify records that exist/do not exist in the target table. Once a prospect has been made a customer then remove the number.

David

 

[djackson]

Thank you all very much for your suggestions. I'm going to play around with each of them and see which works best. You've definately got me in the right direction