Report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andre Adams - 14 Jan 2008 15:28 GMT

Why wouldn't the below query post the 2007 and 2008 Totals in a report? I
get all the other information but I do not get the totals. Can someone help?

SELECT DISTINCTROW tblMoneyManagerName.[Account Name],
Sum(qry2007Totals.[SumOfCommission Amount]) AS 2007,
Sum(qry2008Totals.[SumOfCommission Amount]) AS 2008, [YOY Difference]/[2008]
AS [% Change], [2008]-[2007] AS [YOY Difference], tblMoneyManagerName.[Year
End 2007 Commission], tblMoneyManagerName.[Projected 2008], [2008]/[Projected
2008] AS [% to Projected]
FROM (tblMoneyManagerName INNER JOIN qry2008Totals ON
tblMoneyManagerName.[Account Name] = qry2008Totals.[Account Name]) INNER JOIN
qry2007Totals ON tblMoneyManagerName.[Account Name] = qry2007Totals.[Account
Name]
GROUP BY tblMoneyManagerName.[Account Name], [YOY Difference]/[2008],
[2008]-[2007], tblMoneyManagerName.[Year End 2007 Commission],
tblMoneyManagerName.[Projected 2008], [2008]/[Projected 2008];

Reply to this Message

mscertified - 14 Jan 2008 16:52 GMT

Its probably not a good idea to have columns with numeric names (2007,2008),
I'd change them to Tot2007 and Tot2008. The columns should come through in
the reports if it based on the query that returns them. Totals in a report
are usually summed (in a footer) from the detail record's values, hence there
is no need to do the summing in the query.

-Dorian

> Why wouldn't the below query post the 2007 and 2008 Totals in a report? I
> get all the other information but I do not get the totals. Can someone help?
[quoted text clipped - 12 lines]
> [2008]-[2007], tblMoneyManagerName.[Year End 2007 Commission],
> tblMoneyManagerName.[Projected 2008], [2008]/[Projected 2008];

Reply to this Message

Andre Adams - 14 Jan 2008 16:56 GMT

Thank you for responding.

The 2007 and 2008 numbers will not show in the report. Based off the SQL
view that I posted, what would cause these two columns to be blank?

> Its probably not a good idea to have columns with numeric names (2007,2008),
> I'd change them to Tot2007 and Tot2008. The columns should come through in
[quoted text clipped - 20 lines]
> > [2008]-[2007], tblMoneyManagerName.[Year End 2007 Commission],
> > tblMoneyManagerName.[Projected 2008], [2008]/[Projected 2008];

Reply to this Message

John Spencer - 14 Jan 2008 18:21 GMT

First question: If you run the query by itself do 2007 and 2008 have values?

IF not, then we need to look at the query. If so, then we need to look at
the report and see if the reports record source is what you think it is and
how and where you are referencing the fields in the report.

By the way, naming the fields with all numbers is generally a bad idea.

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Thank you for responding.
>
[quoted text clipped - 36 lines]
>> > [2008]-[2007], tblMoneyManagerName.[Year End 2007 Commission],
>> > tblMoneyManagerName.[Projected 2008], [2008]/[Projected 2008];

Reply to this Message