How to Add feature to query Append ?

[azhar2006]

I did ask if he needed to do updates or if the exchange was simply for new records and I mentioned the two choices when only adds are relevant. What is he supposed to do if there are "duplicates" in the input file? Should the entire file be set aside unless it is corrected? Whenever two applications exchange information, there needs to be a protocol for validation and acceptance of the data. That has not been defined by this poster. I am merely emphasizing the importance of the unique index. NO MATTER WHAT ELSE he does, the index needs to be added. The prevention of duplicates is the job of the database engine. You might do code validation so you can give the user better error messages but the buck stops with the db engine. And if you don't assign this task to the engine by creating a unique index, you are a fool. I am trying very hard to not equivocate I don't want there to be any confusion in the mind of anyone. If you need to prevent duplicates, you need a unique index. The rest of the process is workflow.

This is what the OP said:

Clearly the reply didn't give you the warm fuzzies either but you didn't insist on the index. That is what I am doing. You reiterated the importance of running the find duplicates query. Running the find duplicates query is information but it does NOT prevent adding duplicates. THAT is the important part of the process. We don't even know if anyone cares about the duplicates. They probably do so I would also run the query but it is far more likely that the duplicates are actually there because some piece of other data needs to change. That is why I asked yet again about the need to update existing data.

I would probably go further. Not only would I check for duplicates but if there are duplicates, I would compare each associated field. If they are all the same, I would simply ignore the duplicate. Why make work you don't need to. If they are different, then there is something amiss and it needs to be corrected.

I think our big brother @The_Doc_Man knows what the military number is. It is a number that accompanies the employee from the time he enters the service until his retirement. It is impossible for two employees to have the same number, because this number consists of rows of numbers, each two numbers refer to a specific code of information, and it is always approved and even printed on the metal necklace. It is impossible for it to be repeated except for the same person. For example, this soldier was working in Unit A and then transferred to Unit C after four years. When it is repeated, we know that Unit A did not remove it from its database. I think my big brother @The_Doc_Man knows what I am talking about.

 

[azhar2006]

We are very likely having a language issue. Just answer yes or no. Did you add the unique index on the MilitaryNumber? It also needs to be required and the default should be NULL and NOT 0 if it is an actual number.

No, I did not add it.

 

[Pat Hartman]

Do you intend to add it? Simply checking for duplicates will never stop them from being added to your table. The only way to ensure that no duplicate ever gets added is to add the unique index AND set the Required property to Yes and leave the Default value empty. Was I not clear? I do not know how to make you understand the importance of this. If you do not add the unique index, you will end up with duplicates in the table if someone runs the append query.

Also, I would not store the MilitaryNumber as a long integer since it is actually a code and not a number on which you would ever perform arithmetic. But, that is just a recommendation based on many years of experience. Do what you want with that. For those of you in the US, I never stored the SSN as a long integer in any application I designed that collected it and since the SSA started adding letters to the string a few years ago, I was rewarded because none of my applications needed to be changed A code is a code. It is not a number. It may be a number in its source application where it is generated but in all other applications, it is simply a unique reference code. The military has fewer members than the general population so they probably have some years to go before they run into having to reuse old numbers or increase the length or start using letters. So, what happened to the SSN will at some point happen to the MilitaryNumber. It will ultimately have to be expanded in length or include the use of letters.