?
Solved

Aggregate Function error

Posted on 2016-10-12
3
Medium Priority
?
80 Views
Last Modified: 2016-10-12
Experts,

Why do I get an aggregate function error tblRepayment.ID_FAcility in the QryTEST in the attached?

SELECT qryRepaid.FacilityAmount, (Select Sum(T.Amount) From qryRepaid As T Where T.ID_Facility=qryRepaid.ID_Facility And T.ValueDate <= qryRepaid.ValueDate)-Sum([Amount]) AS [Beginning Balance], [facilityamount]-[beginning balance] AS bal
FROM qryRepaid
GROUP BY qryRepaid.FacilityAmount;

Open in new window


thank you
BalanceEE---WC.accdb
0
Comment
Question by:pdvsa
3 Comments
 
LVL 40

Expert Comment

by:PatHartman
ID: 41840838
Try putting the subtraction AROUND the subselect rather than inside it.

SELECT qryRepaid.FacilityAmount - (Select Sum(T.Amount) From qryRepaid As T Where T.ID_Facility=qryRepaid.ID_Facility And T.ValueDate <= qryRepaid.ValueDate)-Sum([Amount]) AS [Beginning Balance] AS bal
FROM qryRepaid
GROUP BY qryRepaid.FacilityAmount;
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 41841017
Just add those two fields in the aggregation:

SELECT 
    qryRepaid.FacilityAmount, 
        (Select Sum(T.Amount) From qryRepaid As T 
        Where T.ID_Facility=qryRepaid.ID_Facility And T.ValueDate <= qryRepaid.ValueDate)
        -Sum([Amount]) AS [Beginning Balance], 
    [facilityamount]-[beginning balance] AS bal
FROM 
    qryRepaid
GROUP BY 
    qryRepaid.FacilityAmount, 
    qryRepaid.ID_Facility, 
    qryRepaid.ValueDate;

Open in new window

/gustav
0
 

Author Closing Comment

by:pdvsa
ID: 41841269
Hi Gustav, that works.  thank you...:)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

807 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