Access Report Groupings - Sum Groups for All? 1

[misscrf]

I have a report that is pulling information for car costs.

There is Car A, Car B and Car C

For each car there are car costs entered on different dates, and they are also categorized into types.

The types can be Gas, Maintenance, Wash, Body Work.

So the report has a car header, then a cost type header then a detail, car cost type footer and car footer and report footer.

It looks like this:

Car A

Cost Type Body Work - $50
Cost Type Gas - $200
Cost Type Maintenance - $35

Car A Total - $285

Car B

Cost Type Body Work - $150
Cost Type Gas - $215
Cost Type Maintenance - $125

Car B Total - $490

Fleet Total - $ 775

----

What I need is to have the total of each cost type for the whole fleet before the fleet total. I need this:


Fleet Total

Cost Type Body Work - $200
Cost Type Gas - $415
Cost Type Maintenance - $160

Fleet Total - $ 775

Can anyone please point me in the right direction? I have been trying to put the cost type and counts in different sections but nothing is working. :-(

Thanks!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

The Fleet Totals can be created with a subreport based on a totals query.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

ahhh. Thanks for responding, Dhookom. Do I put that in the report footer?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would put it in the report footer.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thanks. I will definitely try that. Maybe tonight!! You would think I wouldn't have issues like this, but I make a report like this so rarely, I forget what the heck I am doing lol.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

ok, so I don't know how I am managing it but I am screwing this up and it should be really easy. I have a subreport with the following fields:

CarCostID
CarCostType (linked by fkcarcosttype)
intgallons
CurCostAmount


I made a grouping on the report for carcosttype and put the data in there. In the textbox controls, I am doing a sum of the amounts.

I then put this subreport in the report footer of the main report. First I tried linking the reports on CarCostID, then CarCostType.

When I run the main report, I only get the first car cost type and the total for the whole fleet, at the end of the report. It is the right amount, but how do I get the rest of the types and totals to show up with the first one? When I run the subreport independently, it gives me each type and totals perfectly.

This tells me it is something about how I am putting it in the main report and I can't figure out what. Any other way to link this that I am not seeing?

I appreciate any help that anyone has!

Thank you,

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

The record source of your subreport should present results like:
[tt][blue]
Body Work $200
Gas $415
Maintenance $160[/blue][/tt]


I assume the query might look like:

SELECT CarCostType, Sum(CurCostAmount) as TotCostAmt
FROM [tblNoNameGiven]
GROUP BY CarCostType;

Add the subreport into your original report's footer and make sure the Link Master/Child properties are blank.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

AHHH!!!! It was the links. For some reason I didn't think putting a sub-report without links would be bad or something.

Thanks! Star to you!

misscrf

It is never too late to become what you could have been ~ George Eliot