Want to use Group By in union Query - solution

[Irshad Alam]

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:- " & & " 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

 

[John Spencer]

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:- " & & " MobileNo:- " & [MobileNo] & " PostBox:- " &
[PostBox]
FROM [TeleDir]


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County