Summary in Report Footer

[smil3y]

I have an ACCESS database that logs distances (in a Table Log Sheets) travelled by vehicles on particular dates.

I created a report (DistReport) that allows the user to enter start and end dates and provide a detailed listing of all dates and distances travelled for each vehicle, then at the end of this report it provides the total distance for each vehicle. This is in the details section of the report and looks a bit like this:

Car 1
2 Jan 05 530
2 Feb 05 210
4 Mar 05 110
etc

TOTAL for Car 1 - 850 kms

Car 2
4 Jan 05 230
6 Feb 05 110
10 Mar 05 610
etc

TOTAL for Car 2 - 950 kms

This all seems to work OK.

In the report footer I have a summary that I just want to show the totals for each vehicle. This would look like:


Car 1 850
Car 2 950

For the totals in the footer I created a query - tested it and it worked outside the report - I obtained totals for my entered dates. I then inserted it into the control source for my field in the footer. The control source contains : =[VehicleTotal]![SumUnsealed]. The VehicleTotal query is:

SELECT Sum([Log Sheets].[Distances]) AS SumUnsealed, [Log Sheets].[Car ID]
FROM [Log Sheets]
WHERE ((([Log Sheets].Date) Between [Forms]![DistReport]![Start] And [Forms]![DistReport]![End]))
GROUP BY [Log Sheets].[Car ID]
HAVING ((([Log Sheets].[Car ID])="Car 1"));

I suppose I have two questions :

1. Is this the best way to produce this summary report. ie a SUM query for each vehicle. OR is there a better way.
2. If my approach is OK - then what am I doing wrong. My query just does not seem to run from within the report.

Many Thanks

 

[dhookom]

Create a subreport based on the totals query and insert the totals subreport in the footer of the main report.

A report can have only one record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]