Total fields w/o the fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Right now, I have large form used to collect data. The form includes a date
field, and I was able to figure out how to set parameters so that only
entries from certain dates are shown in the report (using a Totals query,
which sums each category by day). However, in the final report I would like
only one entry per category. Once the date parameters have been set, I want a
single total to appear for ALL dates that have been entered, one total for
each category. While I realize I could sum the fields in the footer, I do not
want the original sums to appear at all. Is this possible? I'll attach the
SQL from the totals query. I can supply any further information needed, and
help is much appreciated!

SELECT [Litter Totals].Date, Sum([Parking Lot Totals].[Parking Hours
Totals]) AS [SumOfParking Hours Totals], Sum([Spaces Totals].[Parking Spaces
Total]) AS [SumOfParking Spaces Total], Sum([Leaf Hours Totals].[Leaf Hours
Totals]) AS [SumOfLeaf Hours Totals], Sum([Litter Totals].[Litter Totals]) AS
[SumOfLitter Totals], Sum([Bike Totals_1].[Bike Paths Totals]) AS [SumOfBike
Paths Totals]
FROM [Leaf Hours Totals] INNER JOIN ([Bike Totals] AS [Bike Totals_1] INNER
JOIN ((([Bike Totals] INNER JOIN [Litter Totals] ON [Bike Totals].Date =
[Litter Totals].Date) INNER JOIN [Spaces Totals] ON [Litter Totals].Date =
[Spaces Totals].Date) INNER JOIN [Parking Lot Totals] ON [Spaces Totals].Date
= [Parking Lot Totals].Date) ON [Bike Totals_1].Date = [Parking Lot
Totals].Date) ON [Leaf Hours Totals].Date = [Bike Totals_1].Date
GROUP BY [Litter Totals].Date
HAVING ((([Litter Totals].Date)>=[forms]![frmReports]![txtDateFrom] And
([Litter Totals].Date)<=[forms]![frmReports]![txtDateTo]));
 
You could remove (not show) the Date field in the query design. Another
method would be to make the detail section of the report invisible.
 
Back
Top