Difficulties in checking the Social Security list

After reading the "XXX local tax bureau Social Insurance report collection system", it is only a simple function, not to consider the problem from the user's perspective. Maybe it is because the project issuer is not a user. None of the core functions: List check! Without emotion: If you have technical strength, you cannot get the list. This is a question worth thinking about.
If it is changed to "my architecture", I only need the user to enter an Excel file for "in-service employees" to automatically complete the check and declaration function!
(The current practice is to develop a Desktop client that allows users to input and then report through the Web service)

The above are some background and lengthy remarks. Of course, the following is an overview of how to do this in the current situation (IDEAS), which should have some reference value.

Step 1: Create an Access database (the company only has nearly 10 million users)

Step 2: export the "in-service personnel list" from the As400 database, including the name and ID card, and save it to the Access Database

Step 3: Find the Access database of "XXX local tax bureau Social Insurance report collection system" and import the name and ID card in the table "STAFF_INFO" to the same database.

Step 4: convert all the above-mentioned ID cards into 15 (in the age of 15-bit and 18-bit mixed storage)

The problem is that the ID cards in both tables may not be correct (the same is true)-Question: Why didn't the original (15-digit) ID cards have a school certificate? Now, I wonder if you have verified your ID card (the mistake may be minimized ).

Step 5: Use the "ID card" field and use Left Join and Right Join to obtain the "uninsured list" and "out-of-office warranty list" respectively (use Is Null for determination) -- because there is an error in the ID card, the reliability of the two lists is low (the same person exists in both lists)

Step 6: generate a temporary table for the above two lists (the temporary table can effectively improve the query efficiency by using Join, Left, Right, and fields including functions)

Step 7: use the "name" field to perform Left Join and Right Join on the two lists again. Then, you can obtain the "list to be insured", "list to be removed", and "List of inconsistent ID cards ".

At this time, the list accuracy is very high, but there may still be errors, that is, if there is a same name, and the company and Social Security ID card is inconsistent, it will appear in both the list

 

Postscript:

At ordinary times, many people may assume that the "name" and "ID card" are completely correct, without considering how to deal with such assumptions. Therefore, pay special attention to what assumptions are made, what conditions are established, and how to deal with them.