Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1404
  • Last Modified:

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

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
tajinderpal
Asked:
tajinderpal
  • 25
  • 23
  • 2
  • +1
2 Solutions
 
QPRCommented:
Does the query have "group by"?
If so, are one of the non-aggregated columns not in the group by list?
0
 
rockiroadsCommented:
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
 
tbsgadiCommented:
if Long Description is a memo you can't group on it.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
tajinderpalAuthor Commented:
We have made no changes to the table when upgrading from A2003 to A2007
0
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
We are not using SQL, we are just using standard Access Database stored locally on the file server.
0
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
The Record Source is  'SC date range'
 
 
0
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
tbsgadiCommented:
Replace POSITION with [POSITION]
0
 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
tajinderpalAuthor Commented:
And the record source is 'march traders'
0
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
The sheet name is t4566801
Where would I add that to the command?
0
 
rockiroadsCommented:
ok, action openquery
what is the query that it is referencing?
0
 
tajinderpalAuthor Commented:
What do you mean??
 
0
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:

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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
Sorry.
The Action Argument is:
Query Name: GET LATEST TRADES
View: Datasheet
Data Mode: Edit
0
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
Trying to figure out in A2007 how to determine linked tables
0
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
when u open linked table manager, what does it display for trades
0
 
tajinderpalAuthor Commented:
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
 
rockiroadsCommented:
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
 
tajinderpalAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 25
  • 23
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now