Report

[Andre Adams]

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];

 

[mscertified]

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

 

[Andre Adams]

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
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?


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];

 

[John Spencer]

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.

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

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
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?


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];