automating matching process

[kcaenj]

I have been developing my macro skills in Excel, but have run into a problem that needs an Access solution.

As of now I have two files that have all employee records for my company. One is for active employees and one is for non-active employees.

I have a file (alldata.xls) with a list of employee IDs (WWID) - in colum A

I want to reference this against the other two lists, looking for the company number (Company_No) and having ONE query that shows if the WWID matches one or the other, or both.

The macro should automate this process, and would also generate an excel file that shows this relationship (WWID_Co_Match.xls)

Right now I have to run this report every couple of weeks and I want to try and create a macro that can do the access portion of the process for me.

Can this be done?

Thanks,

KC

 

[Norie]

Why do you have 2 seperate files?

Why not just have a column/field that indicates whether the employee is active or not?

This could then be used with a filter to get the active/non-active employees.

 

[kcaenj]

They are currently very large documents and are housed individually on the company server. I have pulled them into a access database as two separate tables. I need the separate because sometimes a person that used to be inactive is rehired but they are not removed from list.

for arguments sake lets say I can only have one employee file, any suggestions on how to automate the import and matching of this table with the list of employee IDs that I are new?

Thanks,

KC

 

[Andrew Fergus]

Hi KC

What you seek to do can be done with one query. If you import / link / set up the 3 tables (1 for new, 2nd for inactive and 3rd for active employees) then you can find employees from the new list who are present in either or both of the active and inactive lists with this query (SQL view) :

SELECT T_Emp1.emp1_id, T_Emp2.emp2_id, T_Emp3.emp3_id
FROM (T_Emp1 LEFT JOIN T_Emp2 ON T_Emp1.emp1_id = T_Emp2.emp2_id) LEFT JOIN T_Emp3 ON T_Emp1.emp1_id = T_Emp3.emp3_id
WHERE (((T_Emp2.emp2_id) Is Not Null)) OR (((T_Emp3.emp3_id) Is Not Null));

Please note I used the table names T_Emp1, T_Emp2 and T_Emp3 with employee identifiers of emp1_id, emp2_id and emp3_id. Modify the query for your table and field names.

HTH, Andrew.