+ Reply to Thread
Results 1 to 6 of 6

Union query

  1. #1
    Registered User
    Join Date
    03-26-2012
    Location
    singapore
    MS-Off Ver
    Excel 2007
    Posts
    31

    Union query

    Hi guru(s)- i've just learned the basic of union query. I have 3 query that i need to link together and it's showing error message that union query can't run with multiple values. Can someone help me please

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Union query

    Post your SQL statement for the Union Query so that we can analyze and understand. Your explanation is not sufficient to analyze the issue.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    03-26-2012
    Location
    singapore
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Union query

    SELECT [4 Incidents & Coaching].[Team Member no], [2 Team Member List].Title AS [Position], [2 Team Member List].[Licence no] AS [License ID], [4 Incidents & Coaching].Date AS [Incident date], [2 Team Member List].[ID/FIN No] AS [ID number (NRIC/FIN], [1 Team Member Personal Contact].[Full Name] AS [Name Of Subject], [4 Incidents & Coaching].[Details Of Incident] AS [Background of Case], [4 Incidents & Coaching].[Type of Coaching] AS [Disciplinary Action Taken], [4 Incidents & Coaching].Date AS [Date of Disciplinary Action Taken], Format([date],"mmm") AS [Month], [4 Incidents & Coaching].[Purposes of Coaching], Format([date],"yyyy") AS [Year]
    FROM ([1 Team Member Personal Contact] INNER JOIN [4 Incidents & Coaching] ON [1 Team Member Personal Contact].[Team Member no] = [4 Incidents & Coaching].[Team Member no]) INNER JOIN [2 Team Member List] ON [1 Team Member Personal Contact].[Team Member no] = [2 Team Member List].[Team Member no]
    ORDER BY [4 Incidents & Coaching].Date DESC;
    Union all
    SELECT [5 Variance Record].[Team Member no], [2 Team Member List].Title AS [Position], [2 Team Member List].[Licence no] AS [License ID], [5 Variance Record].Date AS [Incident Date], [2 Team Member List].[ID/FIN No] AS [ID number (NRIC/FIN], [1 Team Member Personal Contact].[Full Name] AS [Name of Subject], [5 Variance Record].[Details of Incidents] AS [Background of case], [5 Variance Record].[Type of Coaching] AS [Disciplinary Action Taken], [5 Variance Record].Date AS [Date of Disciplinary Action Taken], Format([Date],"mmm") AS [Month], [5 Variance Record].[Purposes of Coaching], Format([Date],"yyyy") AS [Year]
    FROM ([1 Team Member Personal Contact] INNER JOIN [5 Variance Record] ON [1 Team Member Personal Contact].[Team Member no] = [5 Variance Record].[Team Member no]) INNER JOIN [2 Team Member List] ON ([5 Variance Record].[Team Member no] = [2 Team Member List].[Team Member no]) AND ([1 Team Member Personal Contact].[Team Member no] = [2 Team Member List].[Team Member no])
    ORDER BY [5 Variance Record].Date DESC , [5 Variance Record].Date DESC;
    union all
    SELECT [3 Daily Attendance].[Team Member no], [2 Team Member List].Title AS [Position], [2 Team Member List].[Licence no] AS [License ID], [3 Daily Attendance].Date AS [Incident Date], [2 Team Member List].[ID/FIN No] AS [ID number (NRIC/FIN], [1 Team Member Personal Contact].[Full Name] AS [Name of Subject], [3 Daily Attendance].[Details of Incident] AS [Background of Case], [3 Daily Attendance].[Type of Coaching] AS [Disciplinary Action Taken], [3 Daily Attendance].[Date of Coaching] AS [Date of Disciplinary Action Taken], Format([Date Of Coaching],"mmm") AS [Month], [3 Daily Attendance].[Purpose of Coaching], Format([Date of Coaching],"yyyy") AS [Year]
    FROM ([1 Team Member Personal Contact] INNER JOIN [2 Team Member List] ON [1 Team Member Personal Contact].[Team Member no] = [2 Team Member List].[Team Member no]) INNER JOIN [3 Daily Attendance] ON ([2 Team Member List].[Team Member no] = [3 Daily Attendance].[Team Member no]) AND ([1 Team Member Personal Contact].[Team Member no] = [3 Daily Attendance].[Team Member no])
    ORDER BY [3 Daily Attendance].[Date of Coaching] DESC;

  4. #4
    Registered User
    Join Date
    03-26-2012
    Location
    singapore
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Union query

    Just posted. Hope to get some help here.
    The error msg i get was;
    The multi valued field [4 incident & coaching.Type of Coaching]"cannot be used in union query.

  5. #5
    Registered User
    Join Date
    03-26-2012
    Location
    singapore
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Union query

    Just posted. Hope to get some help here.
    The error msg i get was;
    The multi valued field [4 incident & coaching.Type of Coaching]"cannot be used in union query.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Union query

    Multi-valued fields although allowable in Access are not good design for RDBMS. I would urge you to eliminate any multi-valued fields and create multiple fields for the information. You have hit one of the reasons they should be avoided.

    Here is a link discussing MVFs. I would just plain stay away from them.

    http://www.utteraccess.com/forum/Mul...-t1991337.html


    Most seasoned developers avoid multi-value fields in tables. Your current query dilemna is just one of them many reasons for that. It's not that they don't "work"; the problem is that they make so many other things much more difficult.
    Last edited by alansidman; 10-28-2016 at 05:02 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with union query of two crosstab queries
    By racefan91 in forum Access Tables & Databases
    Replies: 1
    Last Post: 09-25-2013, 04:02 PM
  2. use of toggle boxes to adjust union query
    By the_adam in forum Access Tables & Databases
    Replies: 0
    Last Post: 06-19-2013, 10:37 AM
  3. Pivot Table from Union Query
    By joogibabu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-31-2013, 08:46 PM
  4. Display Access union query results in excel
    By anoushka in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-26-2012, 11:27 AM
  5. One filter for 3 Queries in Union Query
    By rlsublime in forum Access Tables & Databases
    Replies: 20
    Last Post: 03-20-2012, 05:43 PM
  6. Query Join / Union
    By ciprian in forum Access Tables & Databases
    Replies: 26
    Last Post: 09-14-2011, 01:36 PM
  7. VBA - Help with Union All
    By JohnM3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-31-2011, 05:14 PM
  8. Union
    By Arne Hegefors in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-24-2006, 11:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1