I guess I don't inderstand what "item" you are talking
about. You did mention something about "Project" earlier so
If you want the totals broken down into Project subtotals, I
think you can just make these changes:
SELECT qryMain.yr, qryMain.Project
Sum(qryMain.SumOfamount) As SumMain,
(SELECT Sum(X.SumOfamount)
FROM qryMain As X
WHERE X.yr = qryMain.yr - 1
AND X.Project = qryMain.Project) As SumX,
SumMain - SumX As Diff
FROM qryMain
GROUP BY qryMain.yr, qryMain.Project
OTOH, a crosstab query makes some sense now. You didn't say
what you were using for the RowHeading, but I assume it's
the "project" field.
To deal with the Null values, use the Nz function around the
year amount field in the calculation. I don't have a grip
on what calculation you are doing in this context so I can't
provide the exact changes you should make.
--
Marsh
MVP [MS Access]
Thanks Marshall, but I still have the problem that the sum of amounts is
occurring over the entire set of records and not over each item amount for
the two selected years...
How about this approach? I tried making a CrossTab query where the years are
made into columns and the value is the amount...The problem I get there is
that I need to do some math calculations on the values, but in some areas
there were no expense amounts for a particular year...the crosstab query
appropriately puts no value there, but if, for example, I subtract one year's
amount from a valueless one I get a blank...same if I subtract a nonvalue
from a value...blank...how can I make those null values into zero values?
:
Sheesh, I must have been half asleep when I posted that. I
not only forgot the GROUP BY clause but I combined the
calculations so you'd get another error if you fixed the
GROUP BY. This time I actually tried it to make sure it
works on my end:
SELECT qryMain.yr,
Sum(qryMain.SumOfamount) As SumMain,
(SELECT Sum(X.SumOfamount)
FROM qryMain As X
WHERE X.yr = qryMain.yr - 1) As SumX,
SumMain - SumX As Diff
FROM qryMain
GROUP BY qryMain.yr
Ray S. wrote:
Here's the problem I run into...I used
calling my Main query qryMain
yr being the year field
the amount field is an aggregate sum of individual expense amounts from the
underlying table in the query
SELECT qryMain.yr, Sum(qryMain.SumOfamount)
- (SELECT Sum(X.SumOfamount)
FROM qryMain As X
WHERE X.yr = qryMain.yr - 1)
FROM qryMain
If I create a simple instance of just two entries for expenses for the years
2006 and 2007, the expression seems to work fine; but when there are multiple
entries the expression gives wildly out of whack results...I try to modify it
experimenting but I get errors like that my SumOfamount is not part of an
aggregate function...
:
Ray S. wrote:
I have a report that groups annual business expenses by segment and project
type. The first grouping is on Segment, then within each Segment I group the
ProjectType and order them by the identifying year. The report then returns
the details of individual Projects for each year along with their Expense. In
a group footer I sum the total of Project Expenses for each ProjectType. What
I would like to do is add a footer or line to the report that will show the
difference in Expense amounts for ProjectTypes (not individual projects)
between two comparison years.
Now I get, let's say:
Segment
ProjectType
yr, e.g., 2007
project A $
1
project D $ 2
ProjectSum: $3
yr, e.g., 2006
project M $5
ProjectSum: $5
I'd like to add
(2007 ProjectSum - 2006 ProjectSum) to show the increase or decrese in
annual expenses for ProjectTypes
This kind of aggregation/summary is best done by using a
subreport based on a query that calculates the differences.
Let's say the name of the query for your main report is
qryMain, then the subreport's record source query could be
something like:
SELECT qryMain.Year, Sum(qryMain.amount)
- (SELECT Sum(X.amount)
FROM qryMain As X
WHERE X.yearfield = qryMain.yearfield - 1)
FROM qryMain
