Crosstab Query Totals

%3CLINGO-SUB%20id%3D%22lingo-sub-2041214%22%20slang%3D%22en-US%22%3ECrosstab%20Query%20Totals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2041214%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20save%20the%20totals%20line%20for%20a%20Crosstab%20query%20in%20Datasheet%20view%20so%20when%20I%20run%20the%20query%20the%20total%20are%20there%20and%20I%20do%20not%20need%20to%20press%20the%20'Totals'%20button%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2041214%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2041873%22%20slang%3D%22en-US%22%3ERE%3A%20Crosstab%20Query%20Totals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2041873%22%20slang%3D%22en-US%22%3EUnfortunately%2C%20it's%20not%20quite%20clear%20what%20you%20want.%20Can%20you%20provide%20an%20example%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2043768%22%20slang%3D%22en-US%22%3ERE%3A%20Crosstab%20Query%20Totals%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2043768%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F126074%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3EAfter%20I%20run%20the%20cross%20tab%20query%2C%20i%20need%20to%20press%20the%20'Totals'%20button%20at%20the%20top%20each%20time.%20Is%20there%20a%20way%20to%20default%20so%20that%20the%20it%20totals%20when%20i%20run%20the%20query%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22talmrubin2_0-1610116861528.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F244902i1FAFEE3D918CA943%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22talmrubin2_0-1610116861528.png%22%20alt%3D%22talmrubin2_0-1610116861528.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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?

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

@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?

 

talmrubin2_0-1610116861528.png

 

 

@talmrubin2 

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. 

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 [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.

@George HepworthThanks! below is the sql, any help would be appreciated. I am going to play around with this myself also

 

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];

@talmrubin2 

Without sample data to work with, this is totally air code.

Create a second query like this: 

TRANSFORM Count(past_due_data.[Order No]) AS [CountOfOrder No]
SELECT "Total" As [delivery terms],
"" AS [shipping code*]
FROM past_due_data
INNER JOIN plant_lookup
ON past_due_data.site = plant_lookup.[plant number]
PIVOT plant_lookup.[Plant Name]

 

Save it as DeliveryTotals, or using a name consistent with your naming conventions.

 

Now, create a third query using your original and this new Totals query

 

SELECT * 

FROM YourOriginalQueryName

UNION 

SELECT * 

FROM DeliveryTotals;

 

It may be necessary to tweak my totals query a bit, but that should be idea.