If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Want to use Group By in union Query - solution
I have made query from 2 different table which works fine :
SELECT [Customer], "Address:- " & [Address] & " Telephone:- " & [Telephone] & " Fax.:- " & [Fax] AS CustomerDetails FROM [MachHist] UNION SELECT [CoName], "TeleNo:- " & [TeleNo] & " FaxNo:- " & [FaxNo] & " Email:- " & [email] & " MobileNo:- " & [MobileNo] & " PostBox:- " & [PostBox] FROM [TeleDir]; But when I try add to Group By at the end , it produces error : SELECT [Customer], "Address:- " & [Address] & " Telephone:- " & [Telephone] & " Fax.:- " & [Fax] AS CustomerDetails FROM [MachHist] UNION SELECT [CoName], "TeleNo:- " & [TeleNo] & " FaxNo:- " & [FaxNo] & " Email:- " & [email] & " MobileNo:- " & [MobileNo] & " PostBox:- " & [PostBox] FROM [TeleDir] GROUP BY Customer.MachHist, CoName.TeleDir; It producess error of Specified Expression of Aggregate Function. My Requirement is to group by the customer and CoName, so that there will no duplicate. Please advice. Regards Irshad |
#2
|
|||
|
|||
Want to use Group By in union Query - solution
A UNION query already removes duplicates, so even if you could apply a group
by the results would be the same. If you want the duplicates you could use UNION ALL in place of the UNION In addition you have several syntax problems. 1) You list fieldname.tablename when trying to reference a field which is the wrong order for specifying a field. It should be TableName.FieldName 2) If you want to use a GROUP BY clause then you have to Group by every field in the select clause or if a field is not in the group by clause you have to use one of the aggregate functions (Max, Min, First, Last) on the field in the SELECT clause. 3) You cannot refer to a table.field unless it is in the Query clause. So you can't refer to MachHist.Customer field in the second query string since the table is not referred to in the FROM clause of the second query. SELECT [Customer] , "Address:- " & [Address] & " Telephone:- " & [Telephone] & " Fax.:- " & [Fax] AS CustomerDetails FROM [MachHist] UNION SELECT [CoName], "TeleNo:- " & [TeleNo] & " FaxNo:- " & [FaxNo] & " Email:- " & [email] & " MobileNo:- " & [MobileNo] & " PostBox:- " & [PostBox] FROM [TeleDir] John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Irshad Alam wrote: I have made query from 2 different table which works fine : SELECT [Customer], "Address:- " & [Address] & " Telephone:- " & [Telephone] & " Fax.:- " & [Fax] AS CustomerDetails FROM [MachHist] UNION SELECT [CoName], "TeleNo:- " & [TeleNo] & " FaxNo:- " & [FaxNo] & " Email:- " & [email] & " MobileNo:- " & [MobileNo] & " PostBox:- " & [PostBox] FROM [TeleDir]; But when I try add to Group By at the end , it produces error : SELECT [Customer], "Address:- " & [Address] & " Telephone:- " & [Telephone] & " Fax.:- " & [Fax] AS CustomerDetails FROM [MachHist] UNION SELECT [CoName], "TeleNo:- " & [TeleNo] & " FaxNo:- " & [FaxNo] & " Email:- " & [email] & " MobileNo:- " & [MobileNo] & " PostBox:- " & [PostBox] FROM [TeleDir] GROUP BY Customer.MachHist, CoName.TeleDir; It producess error of Specified Expression of Aggregate Function. My Requirement is to group by the customer and CoName, so that there will no duplicate. Please advice. Regards Irshad |
Thread Tools | |
Display Modes | |
|
|