How to Add feature to query Append ?

[azhar2006]

Hello everyone

Again I need help.

I have a table containing employee information and among this information is the employee card number which is a unique number as you know. I have created a query Append the data and it works in a new way and updates the data in the other database which is in part D. What I want is that the query compares the employee card number, if it finds two similar numbers it passes this record and ignores it. If there is no similar number to the card number it adds this record.

Thanks everyone

 

[Gasman]

Similar?
That sounds very flaky.
If it was the same, you could use a sub query and Not In() perhaps?

 

[LarryE]

Use the query wizard and build a Find Duplicates query.

 

[Pat Hartman]

Creating queries that update the same data in multiple tables is poor practice as I'm sure someone has already mentioned.

 

[arnelgp]

you can just create a Unique index on Employee Card Number field, and just carry on with your normal Append query.
Duplicate Employee Card Number will not be added on this way.

 

[azhar2006]

Use the query wizard and build a Find Duplicates query.

Thank you LarryE dear

INSERT INTO tblMaster ( IDEmployee, MilitaryNumber, FirstName, Unit ) IN 'D:\Data24.accdb'
SELECT tblMaster.IDEmployee, tblMaster.MilitaryNumber, tblMaster.FirstName, tblMaster.Unit
FROM tblMaster;

I have attached this example for you. There is a main database located in part D. When I run the query, this message appears. Some may ask why I am doing this work.

There are departments and units that have been provided with a copy of this database. All they have to do is enter their employees only. Then bring it to the concerned employee in the main department, who in turn runs the query only to transfer the records from the database located in the flash memory to the database located in part D. In the end, I will get more than 20 thousand employees from the copies that were distributed.

 

[XPS35]

It looks like the error occurs when you try to insert duplicate records.

Why are you doing this work?

 

[Pat Hartman]

So, when you get a database from a remote location, your main database process will be a form that lets you link to the table in the database and run what is known as an "upsert" query. This uses a right join and allows you to both add new records and update existing records in one query. As others have mentioned, the MilitaryNumber must be unique so it should have a unique ID in both databases.

 

[LarryE]

Thank you LarryE dear

INSERT INTO tblMaster ( IDEmployee, MilitaryNumber, FirstName, Unit ) IN 'D:\Data24.accdb'
SELECT tblMaster.IDEmployee, tblMaster.MilitaryNumber, tblMaster.FirstName, tblMaster.Unit
FROM tblMaster;

I have attached this example for you. There is a main database located in part D. When I run the query, this message appears. Some may ask why I am doing this work.

There are departments and units that have been provided with a copy of this database. All they have to do is enter their employees only. Then bring it to the concerned employee in the main department, who in turn runs the query only to transfer the records from the database located in the flash memory to the database located in part D. In the end, I will get more than 20 thousand employees from the copies that were distributed.

1. I changed one of MilitaryNumbers in the tblMain so it is a duplicate of another MilitaryNumber. So, we now have a duplicate MilitaryNumber for testing purposes.
2. I created a Find Duplicates query that finds duplicate MilitaryNumbers in the tblMaster table. I used the Query Wizard.
3. I created a tblEmployee with the same fields as the tblMain.
4. I modified your QueryAppend so that it only accepts MilitaryNumbers that are NOT duplicates in the Find Duplicates query.
5. I ran the QueryAppend and it appended the one MIlitaryNumber in the tblMain that was NOT a duplicate number.

That is how you find duplicates in a table and EXCLUDE them from being appended to another table and/or location.

Note:
I had to delete the Destination DB property in the QueryAppend in order to test it, so you will need to add it back. It was D:\...

 

[arnelgp]

firstly you do not need to add IDEmployee since this is Autonumber field.
you will get error when there is already a duplicate number on the table you
are appending to. here is a simple insert query:

INSERT INTO tblMaster ( MilitaryNumber, FirstName, Unit ) IN 'D:\Data24.accdb'
SELECT tblMaster.MilitaryNumber, tblMaster.FirstName, tblMaster.Unit
FROM tblMaster WHERE tblMaster.MilitaryNumber NOT IN
(SELECT MilitaryNumber FROM tblMaster IN 'D:\Data24.accdb');

 

[azhar2006]

1. I changed one of MilitaryNumbers in the tblMain so it is a duplicate of another MilitaryNumber. So, we now have a duplicate MilitaryNumber for testing purposes.
2. I created a Find Duplicates query that finds duplicate MilitaryNumbers in the tblMaster table. I used the Query Wizard.
3. I created a tblEmployee with the same fields as the tblMain.
4. I modified your QueryAppend so that it only accepts MilitaryNumbers that are NOT duplicates in the Find Duplicates query.
5. I ran the QueryAppend and it appended the one MIlitaryNumber in the tblMain that was NOT a duplicate number.

That is how you find duplicates in a table and EXCLUDE them from being appended to another table and/or location.

Note:
I had to delete the Destination DB property in the QueryAppend in order to test it, so you will need to add it back. It was D:\...

Thank you

LarryE​

dear I modified your QueryAppend so that it only accepts MilitaryNumbers that are NOT duplicates in the Find Duplicates query.

 

[LarryE]

firstly you do not need to add IDEmployee since this is Autonumber field.
you will get error when there is already a duplicate number on the table you
are appending to. here is a simple insert query:

INSERT INTO tblMaster ( MilitaryNumber, FirstName, Unit ) IN 'D:\Data24.accdb'
SELECT tblMaster.MilitaryNumber, tblMaster.FirstName, tblMaster.Unit
FROM tblMaster WHERE tblMaster.MilitaryNumber NOT IN
(SELECT MilitaryNumber FROM tblMaster IN 'D:\Data24.accdb');

True, the query does not require IDEmployee, but I did not get any error when I included it. The query appended the IDEmployee from the tblMain to the tblEmployee, even though it was an Auto Number data type. Interesting. The users should use the Find Duplicates query to find and correct duplicates anyway before running the Append query.

 

[LarryE]

Thank you

LarryE​

dear I modified your QueryAppend so that it only accepts MilitaryNumbers that are NOT duplicates in the Find Duplicates query.

Make sure you use the Find Duplicates query to find and correct duplicate Military Numbers before appending records.

 

[Pat Hartman]

The users should use the Find Duplicates query to find and correct duplicates anyway before running the Append query.

I guess you didn't read my post. Do you update existing records?

If Yes, use an "upsert" query (right join)
If No, and you want to ignore duplicates, simply add the unique index on the MilitaryNumber and run the append query. The duplicates will not be appended but the non-duplicates will be.
If No, and you want to report the duplicates, then run the find duplicates query first so you can report them.

NO MATTER whether it is yes or no, you MUST add the unique index on MilitaryNumber to actively prevent adding duplicates. This is not something you leave to the users to remember to run the find duplicates query.

 

[LarryE]

I guess you didn't read my post. Do you update existing records?

If Yes, use an "upsert" query (right join)
If No, and you want to ignore duplicates, simply add the unique index on the MilitaryNumber and run the append query. The duplicates will not be appended but the non-duplicates will be.
If No, and you want to report the duplicates, then run the find duplicates query first so you can report them.

NO MATTER whether it is yes or no, you MUST add the unique index on MilitaryNumber to actively prevent adding duplicates. This is not something you leave to the users to remember to run the find duplicates query.

It just seemed to me that regardless of how you prevent duplicates from being appended in error, they still need to be identified and corrected by someone.

 

[Pat Hartman]

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:

dear I modified your QueryAppend so that it only accepts MilitaryNumbers that are NOT duplicates in the Find Duplicates query.

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.

 

[azhar2006]

Make sure you use the Find Duplicates query to find and correct duplicate Military Numbers before appending records.

Thank you LarryE Yes it works fine . Note you used the HAVING clause in SQL

SELECT tblMaster.MilitaryNumber, tblMaster.IDEmployee, tblMaster.FirstName, tblMaster.Unit
FROM tblMaster
WHERE (((tblMaster.MilitaryNumber) In (SELECT [MilitaryNumber] FROM [tblMaster] As Tmp GROUP BY [MilitaryNumber] HAVING Count(*)>1 )))
ORDER BY tblMaster.MilitaryNumber;

 

[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.

Thank you dear Pat Hartman . Your words make sense. Certainly some errors occur in the update. For example, those who do not have military numbers or neglected to enter them by the person in charge of data entry. But to find 10 records with errors and correct the error or know who is the data entry person who made the mistake. It is better than getting hundreds of records with errors. The duplicate query will bring you closer to the results. Your words are clear as day. I know what you are saying. But not all database users are specialists. They are just ordinary computer users, so they will certainly leave a lot of spelling errors. Or hit the keyboard space bar twice and so on.

 

[LarryE]

Thank you LarryE Yes it works fine . Note you used the HAVING clause in SQL

SELECT tblMaster.MilitaryNumber, tblMaster.IDEmployee, tblMaster.FirstName, tblMaster.Unit
FROM tblMaster
WHERE (((tblMaster.MilitaryNumber) In (SELECT [MilitaryNumber] FROM [tblMaster] As Tmp GROUP BY [MilitaryNumber] HAVING Count(*)>1 )))
ORDER BY tblMaster.MilitaryNumber;

ACCESS did that automatically. I just used the Wizard.

 

[Pat Hartman]

Thank you dear @Pat Hartman . Your words make sense. Certainly some errors occur in the update. For example, those who do not have military numbers or neglected to enter them by the person in charge of data entry. But to find 10 records with errors and correct the error or know who is the data entry person who made the mistake. It is better than getting hundreds of records with errors. The duplicate query will bring you closer to the results. Your words are clear as day. I know what you are saying. But not all database users are specialists. They are just ordinary computer users, so they will certainly leave a lot of spelling errors. Or hit the keyboard space bar twice and so on.

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.