Records

[pleasehelpalot]

Deleting employees who left the organization but where not deleted from both tables.

I have two two Access databases with employee tables , say A and B. A has all the employees contained in B. B also has many employees not in A. How do I create a list of the employees names in table B that are not in Table A.
I need to delete the employee records in table B that are not in table A.

Hope someone can help.
Thanks

 

[rac2]

SELECT * FROM tableB
WHERE employee_id NOT IN (
  SELECT employee_id FROM tableA
)

will show you the employees in tableB who are deleted already from tableA. If the list looks right then

DELETE tableB
WHERE employee_id NOT IN (
  SELECT employee_id FROM tableA
)

I suggest that you make a copy of tableB and tableA before you perform the DELETE query. Cause what if I am wrong, or you make a mistake typing. There is no way back.

 

[pleasehelpalot]

Problem is,I don't have an employee_id field, only Fname and Lname in both tables. Won't there be a problem for employees with the same Lname or Lname and Fname? What do I use, Fname, Lname or is there some way to compare both?

 

[rac2]

Yes, use both.

First check to see how bad the problem is, how many folks have the same names. Then find some way to identify them uniquely based on other fields.

Check for same name, different person.

SELECT Fname, Lname, COUNT(*)
FROM tableA
GROUP BY Fname, Lname
HAVING COUNT(*) > 1

Do this also for tableB.

Manually examine the data you have on these people and find a way to distinguish them, with any luck you will find one of two other fields that do this. For convenience in the following, I will call this field columnX, but it might be two or three columns.

SELECT Fname, Lname, columnX, COUNT(*)
FROM tableA
GROUP BY Fname, Lname, columnX
HAVING COUNT(*) > 1

When this query returns no rows, you have a set of columns (fields) which identify the employees uniquely. These are the fields you will use to match rows from the two tables.


To compare the two tables you could replace employeeID with these several columns in an outer join(LEFT JOIN).

SELECT tableB.* 
FROM tableB
LEFT JOIN tableA 
       ON tableA.Fname = tableB.Fname
       ON tableA.Lname = tableB.Lname
       ON tableA.columnX = tableB.columnX
WHERE tableA.Fname IS NULL

This query will yield the rows from tableB which do not have a matching row in tableA.

You should obtain exactly the same result by concatenating the several columns used in the match.

SELECT * 
FROM tableB
WHERE Fname + Lname + columnX NOT IN (
   SELECT Fname + Lname + columnX
   FROM tableA
)

If so, then you can

DELETE tableB
WHERE Fname + Lname + columnX NOT IN (
   SELECT Fname + Lname + columnX
   FROM tableA
)

If the matching columns are not all Text datatypes then you will need to convert them to strings using CStr().

DELETE tableB
WHERE Fname + Lname + CStr(columnX) NOT IN (
   SELECT Fname + Lname + CStr(columnX)
   FROM tableA
)

Lesson Learned: Every table should have an id column.

 

[fneily]

Besides the bad table design, how often do you delete records of any kind? Everytime you delete and then add records you increase the size of the database. If you're going to keep deleting records, then do Compact and Repair right after it.