Query to show all records, but combine those that match?

[snOOp]

I am trying to combine the data from two similar tables into one
query, but I need for all of the records from both tables to show up
and I want the ones that have matching 'emplid' to be combined into
one record showing both the 'empstatus' and 'strole' fields.

The following query works, but does not combine the matching records:

SELECT givenname, sn, empstatus, emplid, ssn FROM dbo_EmpData
WHERE empstatus='A'
UNION ALL SELECT givenname, sn, strole, emplid, ssn FROM dbo_StdData
WHERE strole='untst';

The fields 'empstatus' and 'strole' become one field 'empstatus' when
the query is run. This field displays both 'A' and 'untst' in the
query result and I assume that is why it is not combining the matching
records. I would like it to show the following results:

givenname sn empstatus strole emplid ssn

Instead I get:

givenname sn empstatus emplid ssn
Any ideas on how I can change the query to fix this?

Thank you in advance for any help!

- Craig

[Bob Quintal]

ct******@earthling.net (snOOp) wrote in
news:1f**************************@posting.google.c om:

I am trying to combine the data from two similar tables into
one query, but I need for all of the records from both tables
to show up and I want the ones that have matching 'emplid' to
be combined into one record showing both the 'empstatus' and
'strole' fields.

The following query works, but does not combine the matching
records:

SELECT givenname, sn, empstatus, emplid, ssn FROM dbo_EmpData
WHERE empstatus='A'
UNION ALL SELECT givenname, sn, strole, emplid, ssn FROM
dbo_StdData WHERE strole='untst';

The fields 'empstatus' and 'strole' become one field
'empstatus' when the query is run. This field displays both
'A' and 'untst' in the query result and I assume that is why
it is not combining the matching records. I would like it to
show the following results:

givenname sn empstatus strole emplid ssn

Instead I get:

givenname sn empstatus emplid ssn
Any ideas on how I can change the query to fix this?

Thank you in advance for any help!

- Craig

A single query will be quite messy.Your key field on each table
is emplid, I assume.
Create the union query with only this field.

SELECT emplID FROM dbo_EmpData where empstatus = 'A'
UNION SELECT emplID FROM dbo_StdData WHERE strole='untst'

Don't use the ALL keyword, we only need one emplid.

Now create your second query that starts with the union and left
joins the two tables. Create calculated fields that use an
nz[empdata].[givenname],dbo_StdData.[givenname]) type construct
for each of the common fields, plus the two independent fields.

--
Bob Quintal

PA is y I've altered my email address.