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
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
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
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;
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.
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks