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

0
Utf
2/14/2010 1:02:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
830 Views

Similar Articles

[PageSpeed] 26

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
> 
0
John
2/14/2010 1:40:23 PM
Reply:

Similar Artilces:

want questions
Hello Everyone, I want some set of questions on Word-2007 to check competency level of the user. Please help. -- Ananta Gupta Ananta, please refer to my other 2 postings: just check out the WORD part. If my comments have helped please hit Yes. Thanks. "Ananta Gupta" wrote: > Hello Everyone, > > I want some set of questions on Word-2007 to check competency level of the > user. Please help. > > -- > Ananta Gupta "trip_to_tokyo" wrote: > Ananta, please refer to my other 2 postings: just check out the WOR...

a dll use a class in another lib project directly
Hi, I recently got a product with mutliple libs, dlls in VC++ 6.0. One thing puzzles me is that one of the dlls "directly" uses the classes in another lib project. Ususally we should put modifier _declspec( dllimport ) and _declspec( dllexport ) in the class declaration before the class can be used by another dll, right? But in these shared classes, the modifier is just absent. Do you have any clue? Any hint is much appreciated. JD ...

group mailing
Is there any way that I can conceal the individual addresses when I do a group mailing? If this isn't possible in group mailing, is there another way to do it with MS Mail? All of this applies to OE, WinMail and WLMail. To use the BCC (Blind Carbon Copy) feature, in a new message window, either click View | All Headers and type in the addresses separated by a; Or: Click the To button. You will see your list of contacts and the choice of putting them in the To, CC, or BCC field. Click on an address, then click BCC to add it. Repeat for all the contacts you want. (Note: Mo...

How to use CButton::SetBitmap()
Thank you for trying to help. Simon Jeffries told me that in order to get a bitmap on a CButton I had to set the OwnerDraw flag and override the DrawItem function to do the drawing, or have a CBitmapButton variable and use the LoadBitmaps function to set the bitmaps for pressed/depressed etc. Wow that seems like a lot of work and I don't think that even involves CButton::SetBitmap(). Can anyone send me a working code chunk that illustrates how to use CButton::SetBitmap() ? Thanks to Simon Jeffries for the help so far. Thanks. Ivan Have you looked at tons of samples at www.codeguru...

I want more!
I need to extend a form that aleady has some 100 lines. For instance, one column has the date, but only until tomorroq. How do I create more lines based on the same formula? Thank you JB I usually select the last row in the range and copy it down a few rows. If you're using xl2003, you may want to look at Data|List. And if you're using xl2007, this feature has transformed into Tables. (Data tab on the ribbon|Data tools Group|What if Analysis|Data table) JB wrote: > > I need to extend a form that aleady has some 100 lines. For instance, one > column has the da...

using LDIFDE to export/import proxyaddresses
Hello, Here is my question. We are installing a software that might affect users' proxyaddresses. To be on the safe side, I did an ldifde export of all the users' proxyaddresses so we can import them if needed. The problem we are having is that eventhough when using logparser to modify a csv and convert it to an ldf file to format the file, we run into the issue that proxyadddreses is a multivalued attribute. Does anyone have a way to do this. Are you trying to add proxyAddresses, or simply export them? -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.e...

I don't want a page of the same label, I want one of each...
I can't find where to tell Pubisher that I want one of each label and not a page of each label. I'm using Publisher 2003 (Office 2003) I see a post that says to enter "<<Next Record>>" but Publisher doesn't see this as a field, just as text. This can't be that hard, but I can't figure it out... Any help would be appreciated. Kelvin I see that it prints correctly, but the print preview displayes it wrong.... I think I have it figured out... "Kelvin" <someone@domain.com> wrote in message news:OSpLwThUIHA.5508@TK2MSFTNGP04.phx....

Using MFC ActiveX control in ASP
I'm using VS.Net 2003 to create a very simple ActiveX control in MFC. The control has no ui and just a few properties and methods. What do I need to do to make it work in a classic ASP page? Currently any time I try to set a property on my control {after calling 'Set obj = Server.CreateObject(...)'} I get this error: Error Type: (0x8000FFFF) Catastrophic failure I can successfully use my control from a C# Winforms app. What am I missing? -Brett- > What do I need to do to make it work in a classic ASP page? It needs a standard COM and/or an IDispatch interface. Does i...

How to export the search result when using Advanced find in Outloo
I am using Outlook 2003 and Exchange 2003 and want to count the total email size used at different timeline e.g. current month, last month, or a period of dates specified etc. but seems Outlook does not provide a ready-made view for such sort. I therefore use the Advanced find to specific the criteria, search result displayed but it does not give me what I want i.e. total size, unless it could export out to a table. Can someone tell me how to export out the search results? Maybe this works for you: In the advanced search click File/Save as search Folder. Then in that search...

Need query of all records based on month
How can i create a parameter query in the query grid to ask for all records based on the month. example: i want all records for May 2007. any ideas? In article <21EC5C38-AB39-4AA1-88D8-7C9627A947FE@microsoft.com>, jackle@discussions.microsoft.com says... > How can i create a parameter query in the query grid to ask for all records > based on the month. example: i want all records for May 2007. any ideas? > say you have a date column in your table Field: DatePart("m",[Orders].[OrderDate]) Criteria: DatePart("m",[Enter date:]) In article <21EC5C38-AB3...

Search Using Address Bar Does Not Work
Hi, I'm using IE8 and running Windows XP, SP3. I used to be able to type a word or phrase into the address bar and it would go to a google search and give suggestions. Now when I type something in it just says, "Internet Explorer cannot display page." Any ideas on what's wrong and how to fix? Thanks! Why would you search from the Address Bar instead of from the Search Box? "Angel Fire" <AngelFire@discussions.microsoft.com> wrote in message news:EA8D68E5-B72E-4191-ABCB-F1341078EF14@microsoft.com... > Hi, > > I'm using I...

Text want wrap in text box
I can't get the text to wrap in a text box, word wrap is set to true, I put some text boxes on a sheet today and the text will not wrap, I did this yesterday and it worked fine, I can copy the ones from yesterday and paste them on a new sheet and they work, any ideas? I have rebooted but this did not help. Using Excel 2000 (9.0.6926 SP3) Thanks Paul B If you're referring to a Control Toolbat text box make sure that the MultiLine property is True. -- Jim Rech Excel MVP "Paul B" <pbridgesnews@uga.edu> wrote in message news:%23ovLUkuLEHA.2456@TK2MSFTNGP12.phx.gbl......

Using Outlook 2003 to check netscape mail.
I want to use MS Outlook 2003 to check my netscape mail. I setup the account in MS Outlook for to check netscape mail and it says that it can't log onto the server. Is there anyone who can give me any information on how to fix this? jakebraucht@yahoo.com Won't work. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Jake asked: | I want to use MS Outlook 2003 ...

Using Machine Name in DOS Batch
Hello, I am running a script against a list of SQL Servers using a DOS Batch file. The .bat generates one output file per SQL Server instance. I can use the destination SQL Server names in the output file names, but I can not use the destination machine names. It brings the lcal machine name and use it on all the out put files. Could you tell me how I can achieve that? set dt=none set tm=none for /F "tokens=2-4 delims=/ " %%i in ('date /t') do set dt=%%i%%j%%k for /F "tokens=5-6 delims=:. " %%i in ('echo.^| time ^| find "current"...

Why is .NET 2.0 required to deploy an app using VC2005 C++ Setup project?
I just created a setup project for my mfc app. I noticed that in the prerequisites section the .NET 2.0 is checked by default. My app does not need the framework (at least this is what I think). If I clear the checkbox, build the project, and then try to deploy it on a machine with ..NET 1.1 the installer fails with a message complaining about missing .NET 2.0. I'm not sure if this the installer that needs the framework or my app. Are there any mfc8 dlls dependant on the framework? Thanks Hi, > I just created a setup project for my mfc app. I noticed that in the > p...

Want to Add more Sub description Field
How I can add more sub description field like 1,2,3,4,5 regards Saleem Suri Salaam Saleem, You have only limit up to 3 fields in RMS 1,2,3 sub-description also you can use the extended description and there is main description for the item. so Altogether you have upto 5 description limit and I think that is enough. Let me know if this won't fulfill your requirements else use the Item Notes for more description. Rate if like Regards Akber "Saleem Suri" wrote: > How I can add more sub description field like 1,2,3,4,5 > > regards > Saleem Suri Salam Akbar ...

Net Use Question
I have a simple Workgroup at home and all I want to do is share a drive. Primary computer is set up with Login: phil Password:123 IP 192.168.60.155 Shared Name: e Drive I want: E: I want to set this up so it will auto login every time from my other computer. This is what I used. net use e: \\192.168.60.155\e password:123 /User:phil /Persistent:Yes It tells me the password is bad. If I just use: net use e: \\192.168.60.155\e /user:phil It then prompts me for the pw and i just type in 123 and everything works. How do you get it to take the password? Thanks! ...

Basing a query on the selection in a Combo box parameter
I have a group of engineers polling servers to learn what Middleware is running. I would like to give them a dataset to update as they get the info from the servers. I created a query that asks for a middleware type, and returns the servers that run that middleware. But I realize that there is a difference between WAS and Websphere, and expect users to make that mistake. To me the obvious answer is to force users to select from a fixed list in a combo box. I used Access Help, and found a topic for placing calendar controls in a form to drive the criteria for the query, and mod...

Using VBA to Print PDF Files
Hi: My customer has PDF spec sheets for their inventory items. When a pick ticket is printed, they wish to print the PDF spec sheets for each item on the pick tickets. I am very comfortable with the report event structure and know exactly how to get to the PDF files using VBA, but I am having a problem getting the docs printed. I've taken two approaches: 1. Drop ACROPDF.dll onto a User Form in the VBA project. To do this, I created a procedure on the form as shown: Public Sub PrintPDF(FileName As String) AcroPDF1.LoadFile (FileName) AcroPDF1.printAll PDFPrinter.Hide End Sub T...

Web query
i'am trying to get data to a excel from a web site which has a password to enter. web query option dose not allow this. any ways to overcome this problem?? ...

I want to add a sound event when the cursor bumps left margin
I want to know (via an event sound) that my cursor movement has bumped the left edge of the spreadsheet. I am entering thousand of rows of data, and it would help if there was a event sound when I returned the cursor to the left edge. I have this feature in 123, and find it helpfull. Put this in the sheet module: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Static ColOld As Integer If Target.Column = 1 And ColOld > 1 Then Beep ColOld = Target.Column End Sub -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "m...

Excel wants to create a page break in every cell!
After saving revisions in an Excel worksheet, I get a popup message that states "Margins do not fit page size". After clicking "ok" (because this is my only choice) a page break is automatically created in ever cell. At times(depending on the sizeof the work sheet) it can be over 100 pages with a page size 14% or smaller. Why does this happen, and how can I avoid it? I saw this suggestion in a different forum. Try closing excel. Clean up your windows temp folder Windows start button|Run type: %temp% and hit enter Clean all those files that you ca...

Multiple Group in Reporting Svs
Hi, I would like to design my Report in Matrix format where I have "rows" set to Student RollNo ... and "columns" group is set to Student Subjects ( these subjects are fetched from a table .... it could be 3 or 4 per student). Now, I also need to have another group to be attached to the existing "coulumns" which will display Student Progress (again... the Progress fields are getting fetched from a table .. there are 3 fields) So, over all my Report should be something like this : -------------------------------------------------------------------...

True Memory Use
Helloa, I have developed a app using MFC, dialog based and using dynamically linked MFC libs instead of static. My EXE size is about 300kb (release mode) and yet the task mgr says its using closer to 7MB - I understand that the MFC code may take a fair bit, and the std 1MB for the stack. Is there a way to find out the exact memory usage? (taskmgr seems to fib) as I am trying to work out the system requirements for running 10, 50, 100 copies of this program etc. - Mark R > Is there a way to find out the exact memory usage? Try using Performance Monitor. In Run, type "perfmo...

using TODAY() in MS Query?
I have a query built in MS Query. I'd like to set it up so it returns results only from the previous month (e.g., for this month it would only return results from January). I tried to set the criteria for the date field to >EOMONTH(TODAY(),-2) AND <= EOMONTH(TODAY(),-1) but MS query does not recognize the TODAY() function. Is there something else i can use? rachael MS Query uses Date() instead of Today() Does that help? *********** Regards, Ron XL2002, WinXP-Pro "rachael" wrote: > I have a query built in MS Query. I'd like to set it up so it returns resul...