Results 1 to 6 of 6
  1. #1
    andreei87 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    3

    Union query problem

    Hi guys ... i was wondering if any of you can help me on a little problem i have ... i need to build a SQL UNION query using SELECT, UNION / UNION ALL, WHERE, GROUP BY (no INNER JOIN like the design view in MS Access does). I've been trying to do it for the last few weeks but nothing!



    Here are my tables:
    Employee: id_employee, salary
    penalty: id_employee, penalty, date_of_penalty

    I need to take the 2 tables and create a query to calculate the final salary final_salary=salary-penalty and print the results in a new table with the structure: Table:id_employee, salary, penalty, date_of_penalty, final_salary.


    if any of you can help me i would be very thankful!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Based on the desired result, you don't want a UNION query, you just want to join the two tables.

    SELECT...
    FROM Employee LEFT JOIN Penalty ON Employee.id_employee = Penalty.id_employee
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    andreei87 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    3
    My professor wants a UNION query and the problem is worth 2 points on the exam!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Ah; well, you don't want me doing it for you, so I'll simply say that you'll need some alias fields in your UNION:

    SELECT Employee.id_employee, Employee.salary, 0 as Penalty, 0 As PenaltyDate
    FROM Employee
    UNION ALL
    ...

    I get 2 points; see attached.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    andreei87 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    3
    here is what i have so far but in the final table i get the values 2 times for the employee who has penalty.


    SELECT table.id_employee, table.salary, table.penalty, table.penaltydate, table.[salary]-[penalty] AS final_salary
    FROM (

    SELECT id_employee, salary, 0 as penalty, 0 As penaltydate
    FROM employee
    UNION ALL
    SELECT id_employee, 0 as salary, penalty, penaltydate
    FROM Penalty

    ) AS table
    GROUP BY table.id_employee, table.salary, table.penalty, table.penaltydate;

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Sure; you're grouping by every field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Union ALL problem
    By witooldas in forum Queries
    Replies: 1
    Last Post: 04-29-2011, 07:12 AM
  2. Union Query
    By jlclark4 in forum Queries
    Replies: 3
    Last Post: 02-25-2011, 08:21 PM
  3. Union Query Help
    By jo15765 in forum Queries
    Replies: 7
    Last Post: 01-06-2011, 05:46 PM
  4. Please help me out with union query
    By radicrains in forum Access
    Replies: 1
    Last Post: 10-29-2010, 01:48 AM
  5. Need help with a Union Query
    By jdowdy in forum Access
    Replies: 1
    Last Post: 10-13-2009, 05:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums