Solved

Aggregate Function error

Posted on 2016-10-12
3
59 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 36

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 50

Accepted Solution

by:
Gustav Brock earned 500 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

726 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