How to Add feature to query Append ? (3 Viewers)

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