Calculating columns in crosstab query

[GraemeG]

Hello.

is there a way to calculate columns together in a crosstab query.


The picture attached will help explain.
I need to add the firt five columns to the sums already in the 6th column for example.
As this is set for a report and currently I am doing the calculations in the report which is rgeat but takes too long to load, wheres the non calculated fields are instant.

Is this possible?

[alansidman]

This is untested as I didn't want to recreate your db.

Insert a column and in the first line type

Sum Years 1-10: [Year1]+[Year2]+[Year3]+[Year4]+[Year5]+[Years6-10]

If this does not work, then create a new query with this query as the record source and then insert the above in the second query. Use the second query as your record source for your report.

Alan

[GraemeG]

Sorry it would not let me create a new column. Also I do not understand how you mean create a new query from this existing crosstab query?

[alansidman]

On the ribbon, click on create. Click on Query Design. Click on the tab Queries and select the crosstab query. Add the fields that you want in the new query. Insert a new column and put the expression in the first line of the new column.

Alan

[GraemeG]

This doesnt make sense. Creating anew crosstab from the existing just gives the years so cannot create a valid corsstab query giving the correct responses?

[GraemeG]

Sorry understand now. Cracked it.
I have used the calculations in the report as new column headers it works!!
Thanks

[GraemeG]

Great Its just the totals now for each column. I can set this in normal view of the crosstab. But it does not show up in the report. I have to then add totals in the report design and these are the only things that remain unloaded unless I click on them or wait a very long time. Is there a way to include these in the crosstab somehow?

[alansidman]

I don't understand your last thread. Have you made the last query the record source for your report? If you have, then everything should populate quickly and properly. Without seeing your database, I am only guessing as to what you are doing and am shooting in the dark at a target I cannot see.

Alan

[GraemeG]

Yeah thats right I now have the new query looking at the crosstab query. The new query populates the report and does populate the report quickly. However each column needs a total row. and to do this I can only use the totals function in the report design. which is still causing the load issue. Is there a way to incorporate a total row for each column at the query stage?

[June7]

You can have a Totals row in a table or query but this will not translate into the report.

You are using the report's grouping and sorting with summary calcs in footers? Don't know why this would take so long to load. Would have to examine the project. If you want to, make it available in attachment to post or upload to fileshare site and post link. Either way, remove confidential data and run Compact & Repair first.

[GraemeG]

You can have a Totals row in a table or query but this will not translate into the report.

You are using the report's grouping and sorting with summary calcs in footers? Don't know why this would take so long to load. Would have to examine the project. If you want to, make it available in attachment to post or upload to fileshare site and post link. Either way, remove confidential data and run Compact & Repair first.

Yeah thats right I am using the grouping and sorting totals as footers. It loads very quickly when I open the report straight from the navigation pane when I am developing the dB. But when I load my "Apllication" that I have created which hides runtime etc so and try to open the report from a VBA command within a subform this is when it takes so long to load the totals row. It would be quite difficult for the dB to be sent to you without exposing confidential data, or atleast for you to understand how the report works if I take all the data out.

[June7]

Yes, you would have to remove confidential data (phone, address). Could copy db, remove most records and dummy up the remainder for testing.

Could show the code that opens the report, maybe we can see a problem there.