Running totals without detail (1 Viewer)

[swell]

Hello!
I have a report from a log-book database that produces "activity Book# detail hours mileage" with running totals by activity and book#.
I would like to produce the report without the details just the "activity book# hours mileage" with running totals of hours and mileage.
I get the data I want if I hide the detail lines but this of course produces many blank lines in the report.

 

[Gasman]

Group by Activity and Book?

 

[swell]

Group by Activity and Book?

A picture is worth a thousand words
This is a snippet of the report as it stands.
I am also attaching the SQL of the query used

My guess is I have to modify the SQL, a helping hand would be appreciated.

 

[Gasman]

A picture is worth a thousand words
This is a snippet of the report as it stands.
I am also attaching the SQL of the query used

My guess is I have to modify the SQL, a helping hand would be appreciated.

Not when you have to download and unzip it?
Open your query and click on the Total icon, then group by your two fields and use Count/Sum for whatever you need.
Query is (for others to save downloading it)

SELECT [Member Activity].BookNumber, [Member Activity].[Date of Activity], [Member Activity].Activity, [Member Activity].Hours, [Member Activity].Distance, [Member Activity].Notes
FROM [Member Activity]
ORDER BY [Member Activity].BookNumber, [Member Activity].[Date of Activity];

https://www.youtube.com/results?search_query=create+access+totals+query

 

[Pat Hartman]

Do you have the can grow/can shrink properties for the section set to yes? If you do and the rows are not shrinking, check to make sure that the controls don't overlap. The section won't shrink if the controls overlap.

 

[swell]

Thank you, I have got a working report .

Another thing I need is to have a count of the books associated with each activity. When I attempt to do a count of the book number ("Count of members for this activity") I get a count of the number of activity events. Instead of a count of the number of books. I have =Count([BookNumber]) as the control source.

 

[Pat Hartman]

You need to add a group by books. Then you can count the number of books in that footer.

 

[swell]

You need to add a group by books. Then you can count the number of books in that footer.

The report has a group by "booknumber" and "activity" I added a group just for "booknumber" the total is the same. What is needed is to count unique book numbers for each activity. Each record has a book number for each activity. Is there a way to get a count of unique book numbers?

 

[Pat Hartman]

I can't tell from the report what is missing. Is Activity the top level? Do you want to count the number of books for the activity? If you want to count the number of books across all activities, you will need to create a recap and for that you'll need a separate query and a subreport. You can put the subreport in the report footer so it prints at the end of the report.

Keep in mind that count() counts the total instances in the recordset. The report groups will break that up for you but you need to get the correct grouping. The details may be interfering with this so the most likely solution will be a recap type report as I suggested above.