Combine data from tables into query

[NeedExcelHelp07]

I have two tables. I would like to make a query that includes data that is in
both tables and unmatched data. An example of what I'm tyring to get:

Original:
Table 1 Table 2
Name ID Unique Number Name Emp. Code Status
John Franks g123 99652 John Franks 456 Active
Frank Carr h456 9523 Greg James 321
Inactive

What I'm trying to get:
Query:
Name ID Unique Number Emp.Code Status
John Franks g123 99652 456 Active
Frank Carr h456 9523
Greg James 321 Inactive

Thanks!

 

[Dale Fye]

Assuming that your Name fields are entered the exact same way in both tables,
you could try:

SELECT Table1.Name, Table1.ID, Table1.[UniqueNumber], Table2.[Emp.Code],
Table2.Status
FROM Table1 LEFT JOIN Table2
ON Table1.Name = Table2.Name
UNION ALL
SELECT Table2.Name, Null as ID, NULL as [UniqueNumber], Table2.[Emp.Code],
Table2.Status
FROM Table2 LEFT JOIN Table1
ON Table2.Name = Table1.Name
WHERE Table1.Name IS NULL

BTW, [Name] is a reserved word in Access, and should not be used as a field
name. I recommend changing it to FullName or EmpName, or something like that.

HTH
Dale

 

[NeedExcelHelp07]

Do I need to setup a relationship between the two tables?
I changed the Name fields to Employee Name.

Assuming that your Name fields are entered the exact same way in both tables,
you could try:

SELECT Table1.Name, Table1.ID, Table1.[UniqueNumber], Table2.[Emp.Code],
Table2.Status
FROM Table1 LEFT JOIN Table2
ON Table1.Name = Table2.Name
UNION ALL
SELECT Table2.Name, Null as ID, NULL as [UniqueNumber], Table2.[Emp.Code],
Table2.Status
FROM Table2 LEFT JOIN Table1
ON Table2.Name = Table1.Name
WHERE Table1.Name IS NULL

BTW, [Name] is a reserved word in Access, and should not be used as a field
name. I recommend changing it to FullName or EmpName, or something like that.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have two tables. I would like to make a query that includes data that is in
both tables and unmatched data. An example of what I'm tyring to get:

Original:
Table 1 Table 2
Name ID Unique Number Name Emp. Code Status
John Franks g123 99652 John Franks 456 Active
Frank Carr h456 9523 Greg James 321
Inactive

What I'm trying to get:
Query:
Name ID Unique Number Emp.Code Status
John Franks g123 99652 456 Active
Frank Carr h456 9523
Greg James 321 Inactive

Thanks!

 

[Dale Fye]

No. Then LEFT JOIN clause in the query does this.

BTW, the only way to do a Union query like this is in the SQL view. You can
create the first part in the query grid, but must then switch over to the SQL
view to add the UNION ALL and the second part of the query.

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

Do I need to setup a relationship between the two tables?
I changed the Name fields to Employee Name.

Assuming that your Name fields are entered the exact same way in both tables,
you could try:

SELECT Table1.Name, Table1.ID, Table1.[UniqueNumber], Table2.[Emp.Code],
Table2.Status
FROM Table1 LEFT JOIN Table2
ON Table1.Name = Table2.Name
UNION ALL
SELECT Table2.Name, Null as ID, NULL as [UniqueNumber], Table2.[Emp.Code],
Table2.Status
FROM Table2 LEFT JOIN Table1
ON Table2.Name = Table1.Name
WHERE Table1.Name IS NULL

BTW, [Name] is a reserved word in Access, and should not be used as a field
name. I recommend changing it to FullName or EmpName, or something like that.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have two tables. I would like to make a query that includes data that is in
both tables and unmatched data. An example of what I'm tyring to get:

Original:
Table 1 Table 2
Name ID Unique Number Name Emp. Code Status
John Franks g123 99652 John Franks 456 Active
Frank Carr h456 9523 Greg James 321
Inactive

What I'm trying to get:
Query:
Name ID Unique Number Emp.Code Status
John Franks g123 99652 456 Active
Frank Carr h456 9523
Greg James 321 Inactive

Thanks!