• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 709
  • Last Modified:

Sum function in expression builder

Hi experts,

I’m trying to write a sum query through the event builder but I’m doing something wrong and not quite understanding it.

I have a table containing records; each record has a paid amount field.  Each record is assigned a reference number which may be or may be not unique.  IE some records have the same reference number.

What I’m trying to do is create a query that lists the total paid amount by reference number

I have created a query with the table, added a total paid amount field 'Sum([Grants - Master]![Paid Amount])' but get error message 'cannot have agrregate function...'

thanks for your help,

Rob
0
robfendergibson
Asked:
robfendergibson
  • 4
  • 3
1 Solution
 
Harisha M GCommented:
Hi, give the whole query..

do you have


GROUP BY  [reference number]

at the end ?


---
Harish
0
 
robfendergibsonAuthor Commented:
Thanks mgh,

Here is the whole query:
SELECT [Grants - Master].[Req No], [Grants - Master].Date, [Grants - Master].Organisation, [Grants - Master].[Paid Amount], [Total Paid] AS Expr1
FROM [Grants - Master]
WHERE ((([Grants - Master].[Req No])="group by") AND (([Award])=Sum([Grants - Master]![Paid Amount])))
0
 
Harisha M GCommented:
SELECT [Grants - Master].[Req No], [Grants - Master].Date, [Grants - Master].Organisation, [Grants - Master].[Paid Amount] AS [Total Paid] GROUP BY [Grants - Master].[Req No]
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
robfendergibsonAuthor Commented:
mgh,

I have tried this but get error message 'The select statement includes a reserved word or argument that is incorrect or the puncuaution is missing'.  Here is the SQL

SELECT [Grants - Master].[Req No], [Grants - Master].Date, [Grants - Master].Organisation, [Grants - Master].[Paid Amount] AS [Total Paid] GROUP BY [Grants - Master].[Req No]
FROM [Grants - Master];

Access then highlights the GROUP BY as having the problem.  If I remove 'GROUP BY [Grants - Master].[Req No]' it runs ok but obviously do not get otoal paid correctly.

thanks, Rob
0
 
robfendergibsonAuthor Commented:
Sorry Harish, did not see your name until just now...
0
 
Harisha M GCommented:
OOPS.. I left the FROM clause...

SELECT [Grants - Master].[Req No], [Grants - Master].Date, [Grants - Master].Organisation, [Grants - Master].[Paid Amount] AS [Total Paid]
FROM [Grants - Master]
GROUP BY [Grants - Master].[Req No]


Also, it is not a good idea to have Date as field name
0
 
robfendergibsonAuthor Commented:
Sorry mgh,

I thought I had already closed this - my apologies.  It worked fine, thank you.

best wishes, Rob
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now