Exact Duplicate Query

[Dave Titan]

In a DB of 2000+ companies that is being added to every day by dfferent users. I am having a problem creating a company duplicate query to show up the name and number of entries a company has been imputed(or even prevent a company already there being put in again.) the problem stems from the fact that company names may vary a little ie "ABC Computers" may also be entered by someone calling it "ABC computers LTD". At the moment I just run a manual wildcard which is very time consuming - has anyone any ideas?

 

[Alexandre]

You should review your form and table holding the company names information, to split it further into, for example Acronym (or first N letters), full name and type (Ltd, SA, etc.). This is the only way to save you grief in the long run.

Alex

 

[raskew]

Once you have purified your table, it would probably be helpful to revise your input form by adding a combo-box for users to select the company name from a list of already existing names, with the option of adding a new name, if necessary.

This strategy would limit the likelihood of adding an already existing company under a slightly different name.

 

[Rich_Lovina]

The Drop Down List option may be okay for a few records, but it also doesn't tell you if ABC Computers is the same company as ABC Computers Pty Ltd, or any one of 10-20 variations.

Have you looked at running a Query/Make Table with a concatenate field of significant factors e.g. ABC Computers +Postcode+Suburb. Then use a Subform to show the user the existing options.

Remember too that over one year, 15-20% of companies (in most countries) can and do change names/come into business/go out of business/get taken over/or just change names for marketing appeal.

Try tracking business names from y/pages phone book data year-on-year and your problem is further confounded. Hence many factors can be needed to determine if ABC Computers is the same entity. Phone numbers tied to the name(s) also important.

You might best have a ComboBox drop-down tied to four key fields....

Good Luck...depends on how many 1000's of names you have, and enter over time.

 

[Dave Titan]

Thanks very much for the help guys. I'll most likely follow the ComboBox approach as I've been working on the project for the last 8 months and can appreciate how often companies change.

Again thanks for the help, I'll start work on it tomorrow - very much appriciated!