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

Does the query have "group by"?
If so, are one of the non-aggregated columns not in the group by list?

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

if Long Description is a memo you can't group on it.

We have made no changes to the table when upgrading from A2003 to A2007

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?

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

screen-shot.docx

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?

We are not using SQL, we are just using standard Access Database stored locally on the file server.

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

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)

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.

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".

The Record Source is  'SC date range'
 
 

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?

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];

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

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?

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];

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

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

And the record source is 'march traders'

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

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?

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

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

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)

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?

The sheet name is t4566801
Where would I add that to the command?

ok, action openquery
what is the query that it is referencing?

What do you mean??
 

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.

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.

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

Sorry.
The Action Argument is:
Query Name: GET LATEST TRADES
View: Datasheet
Data Mode: Edit

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?

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));

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?

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

OK I think I have found the problem. When I highlight over the Trades table, I see TRADES$ in...........
How can I fix that?

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

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.

Trying to figure out in A2007 how to determine linked tables

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........

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

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......

when u open linked table manager, what does it display for trades

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

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

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....