Solved

you tried to execute a query that does not include the specified expression as part of an aggregate function

Posted on 2009-04-06
51
1,316 Views
Last Modified: 2012-05-06
We have a database that was created in Access 2003. We now upgraded to Access 2007, and when we click a report to run I get the error
you tried to execute a query that does not include the specified expression '[ALL TRADES].[Long Description]' as part of an aggregate function

However it works fine in Access 2003.
0
Comment
Question by:tajinderpal
  • 25
  • 23
  • 2
  • +1
51 Comments
 
LVL 29

Expert Comment

by:QPR
Comment Utility
Does the query have "group by"?
If so, are one of the non-aggregated columns not in the group by list?
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
If this query used to work before in A2003 but now doesnt  then have you made any changes to the table ALL TRADES as part of your upgrade? Did you add this field Long Description in the query recently?

As QPR says, any field not in a aggregate function has to be listed in the group by
0
 
LVL 46

Expert Comment

by:tbsgadi
Comment Utility
if Long Description is a memo you can't group on it.
0
 

Author Comment

by:tajinderpal
Comment Utility
We have made no changes to the table when upgrading from A2003 to A2007
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Are you familiar with SQL? If not then go to design view of the sql

You should see a list of fields. Look in the Total Row, one should have something like SUM or COUNT or AVG, some mathematical type function.

All other fields should have "Group By" in them. Do they? What does Long Description have?
0
 

Author Comment

by:tajinderpal
Comment Utility
I am new to Access, so please can you give me detailed ans.... in how to do things or check settings.
 

screen-shot.docx
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
What are you running to get this error?

If a form/report, open it in design view then look for its rowsourc. Can u post it here?
0
 

Author Comment

by:tajinderpal
Comment Utility
We are not using SQL, we are just using standard Access Database stored locally on the file server.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
yes but when u run a form or report, it has to be based on a query or table.
the error you are getting appears to be a sql related error
0
 

Author Comment

by:tajinderpal
Comment Utility
I am trying to run a report. I have attached the screenshots of the report I am running.
I cannot find the row source. (Where should i be looking)
0
 

Author Comment

by:tajinderpal
Comment Utility
OK, but if it is working fine in Access 2003, then could it be to do with some settings in Access 2007, that is stopping the report from running.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
open a report in design view. then look on the right hand side where it says property sheet
from the dropdown, select Report
click on the Data tab
then have a look at the contents of "Record Source".
0
 

Author Comment

by:tajinderpal
Comment Utility
The Record Source is  'SC date range'
 
 
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
so that is the name of query
so can you now look at the queries, look for SC date range, go to its design view and try to view the sql window. Can u post that here?
0
 

Author Comment

by:tajinderpal
Comment Utility
SELECT [ALL TRADES].[Long Description], [ALL TRADES].xx, [ALL TRADES].POSITION, [ALL TRADES].[NEW ISSUES], [ALL TRADES].OK, [ALL TRADES].[T Login], [ALL TRADES].[Sales Login], [ALL TRADES].[Trd Dt], [EMPLOYEE INFORMATION].NAME, [ALL TRADES].[TRADER/SALESPER]
FROM [EMPLOYEE INFORMATION] INNER JOIN [ALL TRADES] ON [EMPLOYEE INFORMATION].[BLOOMBERG ID] = [ALL TRADES].[TRADER/SALESPER]
WHERE ((([ALL TRADES].xx) Is Not Null And ([ALL TRADES].xx)<>0) AND (([ALL TRADES].OK)=Yes) AND (([ALL TRADES].[Trd Dt]) Between [Enter Start Date:] And [Enter End Date]) AND (([ALL TRADES].[TRADER/SALESPER])=[Sales Login]))
ORDER BY [ALL TRADES].[Trd Dt];
0
 
LVL 46

Assisted Solution

by:tbsgadi
tbsgadi earned 100 total points
Comment Utility
Replace POSITION with [POSITION]
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
that query looks fine in terms of the error as that was complaining about a aggregate function. I have a copy of portable office 2007, I will look at your screenshot using that. docx is A2007, not A2003
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
So this query SC Date Range is taken from the report "totals sales" and that is the report that is failing.

I cant see why, you can sum up a field on a report.

If you ran the query SC Date Range by itself without the report, does it work without errors?
0
 

Author Comment

by:tajinderpal
Comment Utility
How will I run the query?
Also please can you have a look at this:
SELECT [ALL TRADES].[Long Description], [ALL TRADES].xx, [ALL TRADES].[Trd Dt], [ALL TRADES].[NEW ISSUES], [ALL TRADES].OK, [ALL TRADES].[T Login], [ALL TRADES].[As of Dt], [ALL TRADES].POSITION, [EMPLOYEE INFORMATION].NAME, [ALL TRADES].[TRADER/SALESPER], [ALL TRADES].POSITION, [ALL TRADES].Crcy, [ALL TRADES].[Amount Pennies], [ALL TRADES].[TRADER COVER]
FROM [EMPLOYEE INFORMATION] INNER JOIN [ALL TRADES] ON [EMPLOYEE INFORMATION].[BLOOMBERG ID] = [ALL TRADES].[TRADER/SALESPER]
WHERE ((([ALL TRADES].xx) Is Not Null And ([ALL TRADES].xx)<>0) AND (([ALL TRADES].[Trd Dt]) Between [Enter Start Date:] And [Enter End Date]) AND (([ALL TRADES].OK)=Yes) AND (([ALL TRADES].[TRADER/SALESPER])=[T Login]))
ORDER BY [ALL TRADES].[Trd Dt];
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
I looked at the query and couldnt see why it was complaining about a aggregate function.

Just double click on the query, or if you still got it in design view, right click and select datasheet view. Looks like you will get prompted for two dates so enter the dates
0
 

Author Comment

by:tajinderpal
Comment Utility
The first report is running fine now, thank you, your are a life saver.
Now the other report is not working, total traders.
I have attached the screen shot as well.

screenshot2.docx
0
 

Author Comment

by:tajinderpal
Comment Utility
And the record source is 'march traders'
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Looks a similar issue. Can you try repeat the steps, run the query march traders.
When u run the query and it fails, is it with the same error? if so can u post that sql here as well pls
0
 

Author Comment

by:tajinderpal
Comment Utility
Thank you for your help, much appreciated..
The record Source is 'March Trader'
The sql code:
SELECT [ALL TRADES].[Long Description], [ALL TRADES].xx, [ALL TRADES].[Trd Dt], [ALL TRADES].[NEW ISSUES], [ALL TRADES].OK, [ALL TRADES].[T Login], [ALL TRADES].[As of Dt], [ALL TRADES].POSITION, [EMPLOYEE INFORMATION].NAME, [ALL TRADES].[TRADER/SALESPER], [ALL TRADES].POSITION, [ALL TRADES].Crcy, [ALL TRADES].[Amount Pennies], [ALL TRADES].[TRADER COVER]
FROM [EMPLOYEE INFORMATION] INNER JOIN [ALL TRADES] ON [EMPLOYEE INFORMATION].[BLOOMBERG ID] = [ALL TRADES].[TRADER/SALESPER]
WHERE ((([ALL TRADES].xx) Is Not Null And ([ALL TRADES].xx)<>0) AND (([ALL TRADES].[Trd Dt]) Between [Enter Start Date:] And [Enter End Date]) AND (([ALL TRADES].OK)=Yes) AND (([ALL TRADES].[TRADER/SALESPER])=[T Login]))
ORDER BY [ALL TRADES].[Trd Dt];

If I run the March Trader Query, it works fine, asking me for the start and end date.
However when I double click the total traders report, I get the original error?
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Query looks fine to me

The other report, what was done was open and closed but somehow saved. Im wondering if u can try it on this report. Open it, the save it then try running it.
Im wondering if something cocked up due to upgrade.

Also back up this db and try a compact/repair
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:tajinderpal
Comment Utility
Hi I have got the report working......
Now the last problem is in the start up screen (Screenshot added) when I click on 'Get Today Trades' from an Excel file I get the error
'TRADES$' is not a valid name. Make Sure that is does not include invalid characters or punctuation and that it is not too long
 

screen-shot-3.docx
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 400 total points
Comment Utility
TRADE$ looks like the name of the sheet that it is trying to read from (assuming button rads Get Today Trades)

if you go into the design view of this form, right click on the button Get Today Trades and select Build Event. It should take u to the vba editor or macro or something and should have a line possibly starting with DoCmd.TransferSpreadsheet

look at the xls file, open it, does it include TRADE$
0
 

Author Comment

by:tajinderpal
Comment Utility
I have checked the Build event and I do not have a line that starts with DoCmd.TransferSpreadsheet........
What I do Have is 'Action - OpenQuery
Comment is - H:\Excel "S:\Riskmgr_Shared\Full Access\SC PROJECT\LATEST TRADES.xls"
what concerns me is H:\Excel
Is that correct. ( 'H:\' is personal drive)
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
H would be a network drive, maybe automatically mapped on login.
From the message it seems to be missing the shet name. Have you opened this spreadsheet to check if that sheetname exists?
0
 

Author Comment

by:tajinderpal
Comment Utility
The sheet name is t4566801
Where would I add that to the command?
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok, action openquery
what is the query that it is referencing?
0
 

Author Comment

by:tajinderpal
Comment Utility
What do you mean??
 
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Going by what u said. We need to understand what that button is doing

>> I have checked the Build event and I do not have a line that starts with DoCmd.TransferSpreadsheet........

>>What I do Have is 'Action - OpenQuery

If it is opening a query, what query is it opening (see if u can post the sql). What gts me is it seems to be doing an import.

0
 

Author Comment

by:tajinderpal
Comment Utility

The query you are about to run is an append query. Unlike most queries that display data in Datasheet view, an append query adds a set of records to a specific table.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Ok, you got Action OpenQuery on the build event of the button?
You got two columns next to it, Arguments and Comments

Below this though is Action Arguments

Should say

Query Name
View
Data Mode
0
 

Author Comment

by:tajinderpal
Comment Utility
Sorry.
The Action Argument is:
Query Name: GET LATEST TRADES
View: Datasheet
Data Mode: Edit
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Ok, so now what we got is the button you click which says it cannot find TRADE$ is running the query called GET LATEST TRADES

right?

I dont know what it is referring to when it talks about TRADE$ though. Can u post the sql for this query?
0
 

Author Comment

by:tajinderpal
Comment Utility
INSERT INTO t9002711 ( ISIN, [Long Description], [B/S], [Amount Pennies], Price, TBLT, Counterpar, Crcy, [Tkt #], [T Login], [Sales Login], [Trd Dt], [Trd Dept], [As of Dt], xx, [Actual Amount], CUR, PX_YEST_MID, TOTAL, [XCL Date], SumOfTotal, [As of Time], Book, [Long Note1], [Long Note2] )
SELECT TRADES.ISIN, TRADES.[Long Description], TRADES.[B/S], TRADES.[Amount Pennies], TRADES.Price, TRADES.TBLT, TRADES.Counterpar, TRADES.Crcy, TRADES.[Tkt #], TRADES.[T Login], TRADES.[Sales Login], TRADES.[Trd Dt], TRADES.[Trd Dept], TRADES.[As of Dt], TRADES.XX, TRADES.[Actual Amount], TRADES.CUR, TRADES.PX_YEST_MID, TRADES.TOTAL, TRADES.[Short Note1], TRADES.SumOfTotal, TRADES.[As of Time], TRADES.Book AS Expr2, TRADES.[Long Note1], TRADES.[Long Note2]
FROM TRADES
WHERE (((TRADES.[Long Description]) Is Not Null));
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
First glance at query looks okay. If you tried running this by itself, does it fall over?

Looking at the two tables  t9002711  and TRADES, are these tables defined in Access or any linked?

0
 

Author Comment

by:tajinderpal
Comment Utility
Ok I tried running this by itself and it does fall over, same error.
t9002711 query sql code:
SELECT t9002711.ISIN, t9002711.[Long Description], t9002711.[B/S], t9002711.[Tkt #], t9002711.[Amount Pennies], t9002711.Price, t9002711.TBLT, t9002711.Counterpar, t9002711.Crcy, t9002711.[T Login], t9002711.[Sales Login], IIf(IsNull([Sales Login]),[T Login],[Sales Login]) AS [Combine 1], t9002711.[Trd Dt], t9002711.[Trd Dept], t9002711.[As of Dt], t9002711.[XCL Date], t9002711.xx, t9002711.[Actual Amount], t9002711.CUR, t9002711.PX_YEST_MID, t9002711.TOTAL, t9002711.OK, t9002711.POSITION, t9002711.[NEW ISSUES], t9002711.[As of Time], t9002711.Commissions, [TKT TYPE].Definition, t9002711.Book, t9002711.[Long Note1], t9002711.[Long Note2]
FROM t9002711 INNER JOIN [TKT TYPE] ON t9002711.TBLT = [TKT TYPE].[Ticket Type]
WHERE (((t9002711.[B/S])="s") AND ((t9002711.OK)=No))
ORDER BY t9002711.ISIN;
If I run that query works fine.
TRADES:
 Trades is a linked table, from excel
0
 

Author Comment

by:tajinderpal
Comment Utility
OK I think I have found the problem. When I highlight over the Trades table, I see TRADES$ in...........
How can I fix that?
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
so this looks like a linked table then. Have u double clicked it to see if you can open it? If not what is the error msg that it is complaining about? it might say the path of the file it is linked to
0
 

Author Comment

by:tajinderpal
Comment Utility
When I double click the table I get the same error message.
'TRADES$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Trying to figure out in A2007 how to determine linked tables
0
 

Author Comment

by:tajinderpal
Comment Utility
ok I got another problem now, I have entered data into ALL TRADES table.......
and now when I go to run a report for those trade dates I cannot get any information...
However if I run the report on previous days it works fine....
Any reasons as to why it is not working........
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
I think since my version is a portable edition, I am not getting all options available to me. If you right click on the trades table, do you see Linked Table Manager?
if i select it (Im going by A2003 here), does it bring up a list of tables? If any are linked it will show what it is linked to, so if xls file, it will show u the path, what is that path?

rgarding your ALL TRADES issue, I would say maybe no data which is why that is happening
0
 

Author Comment

by:tajinderpal
Comment Utility
Hi, I got the All Trades Issue resolved, the criteria was not being met,
I do get to the Linked Table Manager, however I still get the same error......
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
when u open linked table manager, what does it display for trades
0
 

Author Comment

by:tajinderpal
Comment Utility
When I click on the linked table manager i get two excel links. Screen shot attached.
One is called main and works fine.
The other is called TRADES, and when i check it and click ok, I get the same error.

screenshot4.docx
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
back from the nice long easter weekend and baisakhi also. How was yours. Back to this dreaded problem eh ;)

Ok, looking at this I can see two links

main is pointing to S:\sales credits\client info\client info.xls and the sheet main

TRADES is pointing to s:\Riskmgr_Shared\Full Access\SC PROJECT\LATEST TRADES.xls and the sheet TRADES

Now since it is complaining about TRADES, check that this path is valid and it contains a sheet called TRADES. If it doesnt then that explains your problem.
So you need to correct it accordingly. This this database was setup to look for that xls file with that sheetname, it has to exist. If anything has changed which meant a changed in that xls file, then the database needs adjusting, everywhere where TRADE is used, including recreating link
0
 

Author Comment

by:tajinderpal
Comment Utility
Baisakhi diyan lakh lakh vadian......
I think that might of worked..... Let me just go check it out with the front office users.
Thanks for the Help....
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now