Calculations (1 Viewer)

[khurram7x]

hi,

I'm trying to calculate in a control, in Reports, based on another calculated control. Getting no error, but asking for a value in an input box when i run the report.

In another scenario, I'm trying to put aggregate function in another aggregate function, but is not working as well with an error 'Cannot have aggregate function in expression (function name).

I've attached the screenshot and tried to explain my situation. I could not find the applicable workaround for me situation via Google. I need to make it work. All calculations are in headers and hierarchical fashion. Visible in screenshot.

How could i achieve this please??

Regards,
K

 

[stopher]

Aggregating twice like this has no meaning and probably explains why Access does not like it. Imagine summing the numbers 1 to 3. We get a sum of 6. And then taking the average .... of what... just 6. It doesn't make sense.

Maybe you can explain in words what you are trying to calculate.

 

[JOC]

khurram7x it might be worth doing the calculations in separate queries and then adding them into the query that the report runs off of. I've done quite a bit with calculations in Access and used to find them far easier in a query.

 

[khurram7x]

khurram7x it might be worth doing the calculations in separate queries and then adding them into the query that the report runs off of. I've done quite a bit with calculations in Access and used to find them far easier in a query.

Thanks JOC, but how would I build several level of calculations (Project Manager, Supervisor, Area and WorkPack) in a query. Do you meant you say that i run 3 - 4 queries, each at every level and then stuff them in respective headers??

Maybe you can explain in words what you are trying to calculate.

Thanks. In short, we have 4 levels of grouping. At 4th level of grouping (WorkPack header), I AVERAGE whatever values in Detail section of the report (Record Source is a Query). At 3rd grouping level (Area header), I need to SUM the values of the resultant AVERAGE produced at level 4 so that it'll be the SUM of all averages At level 2 (Supervisor header), I need to SUM, resultant SUM values produced at level 3 (Area header).

Hope it explains?

Regards,
K

 

[JOC]

Could you do a separate report for the project Manager, supervisor etc. each running off their own queries and then use them as sub reports in a larger reports which then shows all the outputs. I've got a whole load of data that I'm trying to pull together onto a single highly complicated application form and I'm going down a sub-report for each section and a larger final report (the completed format) which contains all the sub-reports at the moment.

 

[khurram7x]

Could you do a separate report for the project Manager, supervisor etc.

I understand what you're trying to explain and it is a lot easier than what I'm trying to achieve. But I've been told that this is the standard reporting format we're using in the company for such reports. So I've to make it work somehow (

Attaching the format that i'm trying to acheive, but calculations are wrong... as discussed in original post.

still hoping to find a way working around this limitation.

Thanks

 

[JOC]

Sometimes I forget that you can actually use more than the fields you have immediately available in your tables.

For example the aggregate for work pack one is clearly the sum of all the personal working in all the workpacks in area 1. Why can't you grab this information into a pre-query, make a calculated field in that query and then include the pre-query and that field in the query that the report runs off of? There must be a way that will avoid you putting the calculations directly into each field on the report. I'd aggregate as much as I can into some 'feeder' queries and then use the calculated field from these in a final query that feeds the final report. It won't affect the final format of the report, just the way you get there, and providing you end up with the right values in the right places does it matter how you get there?

I've even used macros to dump data through to access and automated routines to re-import it into Access to feed final reports when Access didn't have a calculation functions available - all sorts of things are possible.

 

[khurram7x]

... aggregate for work pack one is clearly the sum of all the personal working in all the workpacks in area 1. Why can't you grab this information into a pre-query, make a calculated field in that query and then include the pre-query and that field in the query that the report runs off of? There must be a way that will avoid you putting the calculations directly into each field on the report. I'd aggregate as much as I can into some 'feeder' queries and then use the calculated field from these in a final query that feeds the final report. It won't affect the final format of the report, just the way you get there, and providing you end up with the right values in the right places does it matter how you get there?

Yes right, but I give up. I've spent full morning with no results and now I've ran out of ideas. I'm uploading striped down version of the database. There are related tables, queries and couple of reports which i need to sort based on initial discussion.

I'm really grateful for your help.

Khurram

 

[khurram7x]

Could you do a separate report for the project Manager, supervisor etc. each running off their own queries and then use them as sub reports in a larger reports which then shows all the outputs.

Though achieve what i was looking for, but report runs quite slow because of many queries running on the same report.
Thank you, it solved my immediate problem )

Regards,
K