Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Aggregate Function error

Posted on 2016-10-12
3
Medium Priority
?
77 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 39

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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