Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL Union Question

Discussion in 'Information Technology' started by tvh, Nov 17, 2005.

  1. tvh

    tvh
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005
    Sikh News Sikh Union Make Special Visit To Houses Of Parliament To Showcase Their Community Work - Breaking News Jul 30, 2016
    Heritage India's Union Sports Ministry To Promote Gatka History of Sikhism Oct 23, 2013
    Hi-Tech Unites the World's Sikhs in Daily Communion Sikh Sikhi Sikhism Jun 29, 2013
    The Holy Union of Anand Karaj New to Sikhism Mar 16, 2013

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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



    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "tvh" <tvh@discussions.microsoft.com> wrote in message
    news:0B36C835-7054-4245-884F-C5764ED7F3B0@microsoft.com...
    > 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
    >
     
  4. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    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
    __________________________________________

    "tvh" wrote:

    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
     

Share This Page