C
Con Giacomini
Apparently Access doesn't allow you to total DSum'd fields in a report.
So I added the field-to-be-totaled in the query. The detail lines in the
report are correct but the grand total SUM formula multiplies by the number
of detail rows in the query.
For example if the data field has a value of 25, and the query has 10 rows,
my Sum formula in the report shows 250 and not the actual sum of the detail
totals. Here is the SQL for the query:
SELECT DISTINCT Volunteers.[Program ID], Programs.[Program Name],
Programs.[#WSBA Members], Volunteers.[Year Half], Volunteers.[Cal Year],
[Volunteer People Details].[Volunteer ID], [Volunteer People
Details].[Service Type People], [Volunteer People Details].[#Attorneys],
[Volunteer People Details].[#Paralegals], [Volunteer People
Details].[#Others], [Volunteer People Details].[ID People]
FROM (Programs INNER JOIN Volunteers ON Programs.[Program ID] =
Volunteers.[Program ID]) INNER JOIN [Volunteer People Details] ON
Volunteers.[Volunteer ID] = [Volunteer People Details].[Volunteer ID]
GROUP BY Volunteers.[Program ID], Programs.[Program Name], Programs.[#WSBA
Members], Volunteers.[Year Half], Volunteers.[Cal Year], [Volunteer People
Details].[Volunteer ID], [Volunteer People Details].[Service Type People],
[Volunteer People Details].[#Attorneys], [Volunteer People
Details].[#Paralegals], [Volunteer People Details].[#Others], [Volunteer
People Details].[ID People], [Enter Year Half (1 or 2) or Press Enter for
All], [Enter Calendar Year (4 Digits) or Press Enter for All]
HAVING (((Volunteers.[Year Half])=[Enter Year Half (1 or 2) or Press Enter
for All]) AND ((Volunteers.[Cal Year])=[Enter Calendar Year (4 Digits) or
Press Enter for All])) OR (((Volunteers.[Cal Year])=[Enter Calendar Year (4
Digits) or Press Enter for All]) AND (([Enter Year Half (1 or 2) or Press
Enter for All]) Is Null)) OR (((Volunteers.[Year Half])=[Enter Year Half (1
or 2) or Press Enter for All]) AND (([Enter Calendar Year (4 Digits) or
Press Enter for All]) Is Null)) OR ((([Enter Year Half (1 or 2) or Press
Enter for All]) Is Null) AND (([Enter Calendar Year (4 Digits) or Press
Enter for All]) Is Null));
Is there a way to obtain a correct total for the field [#WSBA Members]?
Thanks for any suggestions.
Con Giacomini
So I added the field-to-be-totaled in the query. The detail lines in the
report are correct but the grand total SUM formula multiplies by the number
of detail rows in the query.
For example if the data field has a value of 25, and the query has 10 rows,
my Sum formula in the report shows 250 and not the actual sum of the detail
totals. Here is the SQL for the query:
SELECT DISTINCT Volunteers.[Program ID], Programs.[Program Name],
Programs.[#WSBA Members], Volunteers.[Year Half], Volunteers.[Cal Year],
[Volunteer People Details].[Volunteer ID], [Volunteer People
Details].[Service Type People], [Volunteer People Details].[#Attorneys],
[Volunteer People Details].[#Paralegals], [Volunteer People
Details].[#Others], [Volunteer People Details].[ID People]
FROM (Programs INNER JOIN Volunteers ON Programs.[Program ID] =
Volunteers.[Program ID]) INNER JOIN [Volunteer People Details] ON
Volunteers.[Volunteer ID] = [Volunteer People Details].[Volunteer ID]
GROUP BY Volunteers.[Program ID], Programs.[Program Name], Programs.[#WSBA
Members], Volunteers.[Year Half], Volunteers.[Cal Year], [Volunteer People
Details].[Volunteer ID], [Volunteer People Details].[Service Type People],
[Volunteer People Details].[#Attorneys], [Volunteer People
Details].[#Paralegals], [Volunteer People Details].[#Others], [Volunteer
People Details].[ID People], [Enter Year Half (1 or 2) or Press Enter for
All], [Enter Calendar Year (4 Digits) or Press Enter for All]
HAVING (((Volunteers.[Year Half])=[Enter Year Half (1 or 2) or Press Enter
for All]) AND ((Volunteers.[Cal Year])=[Enter Calendar Year (4 Digits) or
Press Enter for All])) OR (((Volunteers.[Cal Year])=[Enter Calendar Year (4
Digits) or Press Enter for All]) AND (([Enter Year Half (1 or 2) or Press
Enter for All]) Is Null)) OR (((Volunteers.[Year Half])=[Enter Year Half (1
or 2) or Press Enter for All]) AND (([Enter Calendar Year (4 Digits) or
Press Enter for All]) Is Null)) OR ((([Enter Year Half (1 or 2) or Press
Enter for All]) Is Null) AND (([Enter Calendar Year (4 Digits) or Press
Enter for All]) Is Null));
Is there a way to obtain a correct total for the field [#WSBA Members]?
Thanks for any suggestions.
Con Giacomini