Merging several similar databases into one (table only) (1 Viewer)

[azhar2006]

Hello everyone.
I have three identical databases, each containing the same data, distributed among three employees. Each employee updates the data on their respective database. I want to merge these databases into a single database, eliminating duplication and retaining only the most up-to-date information.

 

[RonPaii]

• Create a new table in the shared database duplicating the other tables adding an employee ID column linked to you employee table.
• Temporarily link or import the 3 tables from the 3 databases.
• Execute 3 queries for the 3 tables filtering out un-needed data and adding the employee ID and excluding the primary key ID of the 3 import tables assuming it exists.
• Remove the 3 import tables.

 

[Gasman]

Crossposted at https://www.accessforums.net/showthread.php?t=91217

 

[isladogs]

@azhar2006
You have been a member at both forums since 2012 and have made almost 830 posts in total.
You should by now understand forum etiquette and the reasons why it matters.

 

[Ken Sheridan]

The key is being able to determine which of each set of duplicated records was the latest edited. If, for instance, the table includes a DateTimeStamp column whose value is updated with the current date/time when a record is edited, you could then merge the three tables into a single one by executing three 'append' queries to insert the records from each into an empty new table. The redundant records could then be deleted with a query like that below which deletes all but those rows with the latest DateTimeStamp value for each employee, assuming that all employees' names are distinct. In the event of the DateTimeStamp values being the same the primary key EmployeeID is brought into play as the tie breaker:

DELETE *
FROM
    Personnel AS P1
WHERE
    EXISTS (
        SELECT
            *
        FROM
            Personnel AS P2
        WHERE
            P2.LastName = P1.LastName
            AND P2.FirstName = P1.FirstName
            AND (
                P2.DateTimeStamp > P1.DateTimeStamp
                OR (
                    P2.DateTimeStamp = P1.DateTimeStamp
                    AND P2.EmployeeID > P1.EmployeeID
                )
            )
    );

 

[azhar2006]

The key is being able to determine which of each set of duplicated records was the latest edited. If, for instance, the table includes a DateTimeStamp column whose value is updated with the current date/time when a record is edited, you could then merge the three tables into a single one by executing three 'append' queries to insert the records from each into an empty new table. The redundant records could then be deleted with a query like that below which deletes all but those rows with the latest DateTimeStamp value for each employee, assuming that all employees' names are distinct. In the event of the DateTimeStamp values being the same the primary key EmployeeID is brought into play as the tie breaker:

DELETE *
FROM
    Personnel AS P1
WHERE
    EXISTS (
        SELECT
            *
        FROM
            Personnel AS P2
        WHERE
            P2.LastName = P1.LastName
            AND P2.FirstName = P1.FirstName
            AND (
                P2.DateTimeStamp > P1.DateTimeStamp
                OR (
                    P2.DateTimeStamp = P1.DateTimeStamp
                    AND P2.EmployeeID > P1.EmployeeID
                )
            )
    );

Thank you very much. This is what I wanted. And I apologize to the moderators if I have violated any instructions; I am truly sorry.

 

[Gasman]

Please pay attention to cross posting and notify each site if you have done so. Simply a matter of courtesy.
You lost valuable assistance on the other site because of the cross post.