Totals in report based on query with subquery (as tought by A.Brow

[Mishanya]

I'm new to Access, so maybe my "solutions" are barbarian, but this is what
I've got.
I've found that Access has no straightforward way of dealing with relative
calculations between different records (values) of the same field. In my case
I needed to query PortfolioValue of Period(n) and compare it to
PortfolioValue of Period(n-1). In my report I needed to put in one line both
PortfolioValue(n) and PortfolioValue(n-1) for several consecutive periods and
also make some calculations on it (like Yield, wich is subtruction of the
former from the latter , etc.).
I could do so helped by the great revelation of the subquery method provided
by Allen Browne in his website. I've created PreviousPortfolioValue variable
using SQL phrase SELECT TOP 1 Demi.PortfolioValue FROM tblPortfolioFlow AS
Demi WHERE Demi.Date < tblPortfolioFlow.Date as an expression. So far so good.
Now I need to calculate the sum of all the periods' yields and divide it by
the PortfolioValue(0) in order to obtain the Quaterly (Yearly) Rate Of
Return. Here problems started.
When trying to put =Sum(Yield) in the report footer, I get error msg
"Multilevel expression GROUP BY is not allowed in subquery", although I have
no totals or groupings in the main query nor in the subquery (and they work
fine). When planting the report into another report and putting
=Sum([MyReport].[Report]![Yield]) I get #error in the textbox.
I'll be grateful if someone can advise on the issue.

 

[Allen Browne]

Yes, that problem is one of the frustrating limitations of subqueries in
Access.

This article gives you 5 alternatives:
Surviving Subqueries - Error: "Multi-level group by not allowed"
at:
http://allenbrowne.com/subquery-02.html#MultiLevelGroupBy

 

[Mishanya]

Hi Allen.
First of all I want to use the opportunity to thank You for sharing Your
vast expirience.
Sometimes putting the problem on the paper (in this case - on the Internet
page) after battering it all day long and getting an expert approval that IT
IS A REAL PROBLEM makes everything look simple. I've found that I can manage
to get the totals without using subquery (wich actually was helpful in
presenting the data and calculating simple arithmetics).
Thank You indeed.

Yes, that problem is one of the frustrating limitations of subqueries in
Access.

This article gives you 5 alternatives:
Surviving Subqueries - Error: "Multi-level group by not allowed"
at:
http://allenbrowne.com/subquery-02.html#MultiLevelGroupBy

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm new to Access, so maybe my "solutions" are barbarian, but this is what
I've got.
I've found that Access has no straightforward way of dealing with relative
calculations between different records (values) of the same field. In my
case
I needed to query PortfolioValue of Period(n) and compare it to
PortfolioValue of Period(n-1). In my report I needed to put in one line
both
PortfolioValue(n) and PortfolioValue(n-1) for several consecutive periods
and
also make some calculations on it (like Yield, wich is subtruction of the
former from the latter , etc.).
I could do so helped by the great revelation of the subquery method
provided
by Allen Browne in his website. I've created PreviousPortfolioValue
variable
using SQL phrase SELECT TOP 1 Demi.PortfolioValue FROM tblPortfolioFlow
AS
Demi WHERE Demi.Date < tblPortfolioFlow.Date as an expression. So far so
good.
Now I need to calculate the sum of all the periods' yields and divide it
by
the PortfolioValue(0) in order to obtain the Quaterly (Yearly) Rate Of
Return. Here problems started.
When trying to put =Sum(Yield) in the report footer, I get error msg
"Multilevel expression GROUP BY is not allowed in subquery", although I
have
no totals or groupings in the main query nor in the subquery (and they
work
fine). When planting the report into another report and putting
=Sum([MyReport].[Report]![Yield]) I get #error in the textbox.
I'll be grateful if someone can advise on the issue.

 

[Allen Browne]

Good news. Yes: pinning down the issue can have the effect of clarifying,
and widening our approach.

 

[Mishanya]

Allen, one more question related to the issue.
I used First and Last functions combined with + and - arithmetics instead of
rebellious summing. The query is ordered DESC by Date so it pulls last (TOP)
records. On the contrary, the report with Totals (wich I plant under the
initial subquery-based-on report dimming the data and showing only the
Totals) is ordered ASC by Date, so the First record is the oldest.
I did so by chance and it seems to work, but to be sure: does the ordering
inside the report prevails upon the ordering of query the report is based on
when using functions like First, Last etc.?

 

[Allen Browne]

The sorting in the report depends on what you put in the Sorting And
Grouping dialog, and this completely overrides any ORDER BY property in the
source query of the report.

I'm not sure about your use of First and Last. People often intend Min
(oldest date) and Max (newest date), which is not the same as First and Last
(though you may already understand this.)

 

[Mishanya]

Thanks, it sums up.
As for using First/Last - my calculations (like subtraction of
PortfolioValue(BeginningOfTheYear) from PortfolioValue(Latest) relate to
Min/Max dates indeed, but use the PortfolioValue itself. I was not sure how
to put (if at all) the expression "PortfolioValue WHERE/HAVING
Max(PortfolioDate)" or something like that in arithmetic formula of unbound
textboxes in the Footer, so I used First/Last instead having the records
ordered in desired order.
Maybe there is more elegant way (certainly, MIN/MAX as a date criteria is
much more logical way), but I'm still rookie and learn only when facing a
problem and solving it. If You may advice on using record-criteria ("use
value of MyField from the record of MyReport where CriteriaField is
Max(CriteriaField)") in textbox formula - I'll appriciate.
Thanks again!