• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 713
  • 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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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