SQL Union Question

[tvh]

I have created the following Union which gives me three columns:
tbleiLastName, # of Records and Position. How would I change the statement
so that I have the record count under each position?

So, from this:
tbleiLastName # of Records Position
Smith 10 Lead Position
Smith 3 Assistant
Smith 1 2nd Assistant
Jones 5 Lead Position
Jones 2 Assistant
Jones 2 2nd Assistant

To This:
Last Name Lead Position Assistant 2nd Assistant
Smith 10 3 1
Jones 5 2 2

SELECT tblEmployees.tbleiLastName,
Count(tblWorkOrderDetail.[tblwodWorkOrder#]) AS [# of Records], "Lead
Position" AS [Position]
FROM tblWorkOrderDetail INNER JOIN tblEmployees ON
tblWorkOrderDetail.tblwodLeadTechnician = tblEmployees.tbleiEmployeeID
GROUP BY tblEmployees.tbleiLastName

UNION SELECT tblEmployees.tbleiLastName,
Count(tblWorkOrderDetail.[tblwodWorkOrder#]) AS [# of Records], "Assistant"
AS [Position]
FROM tblWorkOrderDetail INNER JOIN tblEmployees ON
tblWorkOrderDetail.tblwodAssistant = tblEmployees.tbleiEmployeeID
GROUP BY tblEmployees.tbleiLastName

UNION SELECT tblEmployees.tbleiLastName,
Count(tblWorkOrderDetail.[tblwodWorkOrder#]) AS [# of Records], "2nd
Assistant" AS [Position]
FROM tblWorkOrderDetail INNER JOIN tblEmployees ON
tblWorkOrderDetail.tblwod2ndAssistant = tblEmployees.tbleiEmployeeID
GROUP BY tblEmployees.tbleiLastName;

Thanks

 

[Douglas J Steele]

SELECT tblEiLastName,
Sum(Lead) AS [Lead Position],
Sum(Asst) AS Assistant,
Sum(2nd) AS [2nd Assistant)
FROM
(
SELECT tblEmployees.tbleiLastName,
Count(tblWorkOrderDetail.[tblwodWorkOrder#]) AS Lead,
0 AS Asst, 0 AS 2nd
FROM tblWorkOrderDetail INNER JOIN tblEmployees ON
tblWorkOrderDetail.tblwodLeadTechnician = tblEmployees.tbleiEmployeeID
GROUP BY tblEmployees.tbleiLastName
UNION
SELECT tblEmployees.tbleiLastName,
0 AS Lead,
Count(tblWorkOrderDetail.[tblwodWorkOrder#]) AS Asst,
0 As 2nd
FROM tblWorkOrderDetail INNER JOIN tblEmployees ON
tblWorkOrderDetail.tblwodAssistant = tblEmployees.tbleiEmployeeID
GROUP BY tblEmployees.tbleiLastName
UNION
SELECT tblEmployees.tbleiLastName,
0 AS Lead, 0 As Asst,
Count(tblWorkOrderDetail.[tblwodWorkOrder#]) AS 2nd
FROM tblWorkOrderDetail INNER JOIN tblEmployees ON
tblWorkOrderDetail.tblwod2ndAssistant = tblEmployees.tbleiEmployeeID
GROUP BY tblEmployees.tbleiLastName
) AS Subquery

GROUP BY tblEiLastName

 

[Tom Wickerath]

Have you tried creating a crosstab query, which uses your existing union
query as its source of data? Here is an example of creating a crosstab query
based on several tables, but you can also base a crosstab query on a single
table or query:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I have created the following Union which gives me three columns:
tbleiLastName, # of Records and Position. How would I change the statement
so that I have the record count under each position?

So, from this:
tbleiLastName # of Records Position
Smith 10 Lead Position
Smith 3 Assistant
Smith 1 2nd Assistant
Jones 5 Lead Position
Jones 2 Assistant
Jones 2 2nd Assistant

To This:
Last Name Lead Position Assistant 2nd Assistant
Smith 10 3 1
Jones 5 2 2

SELECT tblEmployees.tbleiLastName,
Count(tblWorkOrderDetail.[tblwodWorkOrder#]) AS [# of Records], "Lead
Position" AS [Position]
FROM tblWorkOrderDetail INNER JOIN tblEmployees ON
tblWorkOrderDetail.tblwodLeadTechnician = tblEmployees.tbleiEmployeeID
GROUP BY tblEmployees.tbleiLastName

UNION SELECT tblEmployees.tbleiLastName,
Count(tblWorkOrderDetail.[tblwodWorkOrder#]) AS [# of Records], "Assistant"
AS [Position]
FROM tblWorkOrderDetail INNER JOIN tblEmployees ON
tblWorkOrderDetail.tblwodAssistant = tblEmployees.tbleiEmployeeID
GROUP BY tblEmployees.tbleiLastName

UNION SELECT tblEmployees.tbleiLastName,
Count(tblWorkOrderDetail.[tblwodWorkOrder#]) AS [# of Records], "2nd
Assistant" AS [Position]
FROM tblWorkOrderDetail INNER JOIN tblEmployees ON
tblWorkOrderDetail.tblwod2ndAssistant = tblEmployees.tbleiEmployeeID
GROUP BY tblEmployees.tbleiLastName;

Thanks