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