Jan 07 2021 11:17 AM
Jan 08 2021 07:36 AM
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.
Jan 08 2021 08:04 AM
@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];
Jan 09 2021 09:01 AM
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*]
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
It may be necessary to tweak my totals query a bit, but that should be idea.