Preventing Duplicates (while importing) (1 Viewer)

[Cavern]

I have a 'Contacts' table, which holds contact names and numbers for a bunch of companies and i'm brining this info over from a number of different tables. I need to allow the same name to appear more than once, but not associated with the same company. I'm using a (relatively) simple append query (which is called from a Macro) to do this...

The problem I have is that if a user runs the append query that I'm using to import the data more than once, then the information is duplicated. Is there some way that I can check to see if the information does not exist as criteria for the query?
Or is there some way that I can remove the duplicated entries after? I'd prefer the first type of solution, but I'm open to both.

Thanks
-Cavern

 

[The_Doc_Man]

You could pre-define the table to have a primary compound key that included the company name and person name. Then allow that key to have no duplicates.

When you did the import, you would get some errors because of key violations, but non-violating entries would be imported successfully.

 

[Cavern]

Unfortunetly I cannot change the primary key.. I am not able to change the structure of the new database. Is there some other way to do it?

 

[Pat Hartman]

As it stands now, the table design allows data that violates your company's business rules to be entered. That can't be good. I suspect that the primary key to the table is an autonumber. It is OK to leave that the primary key. However, you should be able to add a unique index that will enforce your business rules.

Yes, technically it is possible to provide you with a solution that answers your specific question. But that solution is actually a way to correct bad data not prevent it. Your responsibility as a professional developer is to prevent it in the first place. The safest, most certain way to guarantee data integrity is to relegate the task to the database engine. Convince the necessary people to add the unique index.

 

[pcs]

cavern,

-----you wrote-----
I have a 'Contacts' table, which holds contact names and numbers for a bunch of companies and i'm brining this info over from a number of different tables.
-------------------

i suspect this is related to your post here:
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=31673

if the data comes from 'a number of different tables', i also suspect that each table may have used a different set of 'rules'. ie, CompanyName could be: The Frammis Manufacturing Company Inc. or Frammis Manufacturing. and the same applies to the ContactName.

as Pat H. points out, you need to prevent these inconsistencies.

'sanitizing' the old data, probably cannot be done with code. it will require human interpretation and input.


if my suspicions are correct, you have a big project.

al

 

[Cavern]

I understand what you guys are saying, and beleive me, I'm with you. But the reason that I need this, is because I'm working here on a contract (which runs out rather soon), and my boss wants me to leave him a simple way to import data from the older (and certainly very poorly designed) databases.

My intention, was to provide at least a basic toolset for anyone else who happens to work on this database for importing and exporting the data. (ie. automated tools for importing company information, contact information, etc..)

In regards to 'sanitizing' the data, I realize that that is far beyond the scope of anything I'm likely to do (or want to do for that matter ).
On the other hand although the data may be stored poorly, at least its consistently poor (and following its own odd rules). So rather than cutting and pasting one record at a time (which frankly I dont think anyone enjoys) I've been trying to automate most of the tasks that I can (Inserting Companies, Copying contacts, etc..).

For instance, I know that say, all the company contacts are stored in feilds named: 'Contact 1 Name', 'Contact 2 name', 'Contact 1 number' .... Etc... Now I can use a query to copy those contacts into my actual contacts table, and link them to a company already in the database.. The only problem is that if (for some reason), someone decides to run the query more than once, the information all gets added again (so you end up with two copies of the contact information with each person)..... All I need to know is if there's some way to keep this from happening ...

Thank you all for your help (and your suggestions) so far......