Combine 3 Lists

[jonealmass]

I have 3 tables of employees.
1. Employees who started the company
2. Employees who were recruited
3. Employees that were reinstated

I want to create one list of employees. Employees can be on as many as 2 lists. I only want that employee listed once. How do I do that.

 

[alansidman]

Combine the list of employees to one sheet and run a "Remove duplicates" using the built in functions on the Data Tab of the Ribbon.

 

[Joe4]

Combine the list of employees to one sheet and run a "Remove duplicates" using the built in functions on the Data Tab of the Ribbon.

This is posted in the Access part of the forum, so I think that they are looking for an Access solution instead of an Excel one.

In Access, I would create a Union Query (see: https://support.office.com/en-us/ar...e-result-1f772ec0-cc73-474d-ab10-ad0a75541c6e)

So the code for that would be structured something like this:

SELECT Table1.Employee 
FROM Table1
UNION
SELECT Table2.Employee
FROM Table2
UNION
SELECT Table3.Employee
FROM Table3;

 

[jonealmass]

Ok. What if the tables all location as well. So I want the employee number and location. A person could have started at one location and got reinstated at another. How do I do that? I only want the original location.

 

[xenou]

Hi,
apart or before writing the SQL query itself, how would you identify which is an original location as compared to one that is not original? Do you have dates or any other identifying information? This will affect how you write the query.

 

[Joe4]

I only want the original location.

How can you determine that from the data?
Is there a date associated with these fields?
Perhaps some data examples would be in order here.

 

[jonealmass]

Yes. I can create a date field. So the fields would be:
Employee
Location
Date

Thanks for all your help!

 

[ranman256]

it should be 1 table with HireStatus flag: Orig,Reinstate,Recruit

 

[jonealmass]

So one table combiing the three tables:
employee
location
date
hirestatus

 

[xenou]

Since hirestatus will be different in each table, you will not avoid duplicates anymore. You might have to go with some subqueries at this point... first you will want to find the minimum date for each employee, then backtrack to the location that goes with that date.

A possible solution therefore:

select 	
	A.Employee, A.[date]
from
	(
		select employee, location, [date] from table1
		union all
		select employee, location, [date] from table2
		union all
		select employee, location, [date] from table3

	) as A
inner join
	(
		select B.employee, min(B.[date]) as MinOfDate
		from
			(
			select employee, [date] from table1
			union all
			select employee, [date] from table2
			union all
			select employee, [date] from table3
			) as B
		group by B.employee
	) as C
on 
	A.employee = C.employee
	and A.[date] = c.MinOfDate