Crosstab Query Totals

Question

Is there a way to save the totals line for a Crosstab query in Datasheet view so when I run the query the total are there and I do not need to press the 'Totals' button?

george_hepworth · Answer

Unfortunately, it's not quite clear what you want. Can you provide an example?

talmrubin2 · Answer

George_HepworthAfter I run the cross tab query, i need to press the 'Totals' button at the top each time. Is there a way to default so that the it totals when i run the query?&nbsp;&nbsp;&nbsp;

george_hepworth · Answer

talmrubin2&nbsp;Thanks for clarifying. Not that I know of. However, I would not do it that way anyway.I would do this as a UNION query.&nbsp;The first SELECT in the UNION is as you have it know.Then UNION and a second SELECT that does the totals by NOT grouping on Delivery Te? and Shipping Code. I would need to see the actual SQL from your crosstab to attempt an example. However, where you have SELECT [Delivery Te?], you would use "Totals" AS [Delivery Te?], and where you have [Shipping Code*], you would use "" AS&nbsp;[Shipping Code*] to suppress the Grouping on those fields but still have a placeholder value to display.If you need more details, please provide the actual SQL from the Crosstab query to work with.

talmrubin2 · Answer

George_HepworthThanks! below is the sql, any help would be appreciated. I am going to play around with this myself also&nbsp;TRANSFORM Count(Past_Due_Data.[Order No]) AS [CountOfOrder No]SELECT Past_Due_Data.[Delivery Terms], Past_Due_Data.[Shipping Code*]FROM Past_Due_Data INNER JOIN Plant_Lookup ON Past_Due_Data.Site = Plant_Lookup.[Plant Number]GROUP BY Past_Due_Data.[Delivery Terms], Past_Due_Data.[Shipping Code*]PIVOT Plant_Lookup.[Plant Name];

george_hepworth · Answer

talmrubin2&nbsp;Without sample data to work with, this is totally air code.Create a second query like this:&nbsp;TRANSFORM Count(past_due_data.[Order No]) AS [CountOfOrder No]SELECT "Total" As [delivery terms],"" AS [shipping code*]FROM past_due_dataINNER JOIN plant_lookupON past_due_data.site = plant_lookup.[plant number]PIVOT plant_lookup.[Plant Name]&nbsp;Save it as DeliveryTotals, or using a name consistent with your naming conventions.&nbsp;Now, create a third query using your original and this new Totals query&nbsp;SELECT *&nbsp;FROM YourOriginalQueryNameUNION&nbsp;SELECT *&nbsp;FROM DeliveryTotals;&nbsp;It may be necessary to tweak my totals query a bit, but that should be idea.

Forum Discussion

Crosstab Query Totals

Share

Resources