Calculations in report on with one to many situation

[khwaja]

I have a report based on a query using one to many relationahip where each location has many expenditure heads. I have created a report grouped on each location and then I sum each expenditure head in the footer. All goes well except that if I were to count number of locations, the resilting count is wriong and in reality is the number of instances expenditure head was entered. Is there a way, I can also find out how many locations were involved when looking at the expenditure total.

Cheers

AK

Note: Using Access 97 - still.

 

[batteam]

Before you spend too much time on this, consider a subreport based on a query that gives you the total number of locations. Unless your main recordsource query is totaling the locations, with grouping, it can sometimes be hard to get a report total. A quick, simple, small subreport showing the total coutn of locations might by your quickest solution. Good luck.

 

[khwaja]

Thanks. I considered this but practically it was not option as each location wil have certain costs and these should be totaled right underneath each location. Your solution may be suitable for an end of the report total but I would like to see how much is the cost for individual location after every location. I managed to get around this problem by using dcount and dsum function in location footer from an other query but it took ages to have the report running. There must be some way to handle one to many situation like the one I described.

Cheers

AK

Note: Using Access 97 - still.