Issue adding calculated fields into a subquery.

Posted on 2012-09-17
Last Modified: 2012-09-17
I am running into an issue where on each of my subreports on my main report, I need to create two calculated fields:  [% of Total AR], and [% of Total (Unbilled)].  I tried to add these two fields into the main table (not a problem).  Then I went into the main (non-append query:  "qry_Project Manager_HISTORY" and add these two calculated fields, but was unable to do so.

The error I received after adding the two calculated fields then trying to run the main rpt was:
"Subqueries cannot be used in the expression (tbl_ProjManager_History.[AROver75]/[TotalAR]).

The fields I was trying to add, were the:
Sum(([UnbilledOver45]+[ UnbilledOver30])/([TotalUnbilled])

I am not able to do this and can't understand why.  What I am doing wrong?
Question by:bkthom
    LVL 12

    Expert Comment

    Are those fields available within the scope of the sub query?
    LVL 29

    Accepted Solution

    You get that error because in your query, I had revised the names to match the original names since I had mistakenly thought you would not be using them in a calculation.  So when you use [TotalAR] in your calculated field, it thinks you are referring to the summation rather than the field in the table.
    queryThere are 2 ways you could fix this.  Either revise the name in the query such as you see in the image above where I renamed it to xTotalAR.  You would need to update your subreports as well with the new name.
    Or you could include the table in the field reference like so:
    tbl_ProjManager_History.[AROver75] / tbl_ProjManager_History.[TotalAR]

    A problem I see though is if your TotalAR is 0, you would end up with a divide by zero error.  So you would need to add a condition like so.
    IIf (TotalAR=0, 0, AROver75 / TotalAR)
    LVL 29

    Expert Comment

    By the way, you don't need to add the fields [% of Total AR] and [% of Total (Unbilled)] to the table.  They should just be calculated each time in the query.

    Author Closing Comment

    IrogSinta -

    YOU ARE GREAT!  That works.  Thanks for the quick follow up.

    I have to run into a meeting but hit another area that I have a question on.  I don't have time to post a question but when I get out of this meeting (at the latest two hours) I will post it.  It has to do with the field [MonthDelqSum] and how I can just get a sum "1" for a PM that has a Delinquency for a given month rather than summing up ALL the number of projects a PM has delinquent for any given month.  That is the very general idea of my issue.  But like I said, I will post my question in about two hours.

    MANY, MANY, thanks again for all of your assistance!
    LVL 29

    Expert Comment

    @Jared_S, looking at your stats, it looks like you're quickly rising to be another good source for answers here.  In this situation though, I had the advantage of being involved in the previous post found here.

    A hearty welcome to EE!  I wish you well.


    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now