Creating a query with yearly totals and monthly side-by-side

[Harris@Z]

Hi all,

I have created a query for totals by year, and another for totals by month (and able to select the month in question)
Is it possible to create a query which combines both, i.e., yearly totals, and including the months? (potentially being able to select the month in question)

See image.

My coding for the yearly totals:
TRANSFORM Count(tempQryTA.TheMonth) AS CountOfTheMonth
SELECT tempQryTA.Test, Count(tempQryTA.TheMonth) AS Total
FROM tempQryTA
GROUP BY tempQryTA.Test
PIVOT tempQryTA.Year;


SELECT Format$([DateReport],'yyyy',0,0) AS [Year], TestsAvailable.Test, Format$([DateReport],'yyyy/mm',0,0) AS TheMonth
FROM TestsAvailable RIGHT JOIN (((Companies INNER JOIN Orders ON (Companies.Company_ID = Orders.Company_ID) AND (Companies.Company_ID = Orders.Company_ID)) LEFT JOIN Samples_List ON Orders.Order_ID = Samples_List.Order_ID) LEFT JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) ON TestsAvailable.Test_ID = SampleTestedFor.TestedFor
WHERE (((Format$([DateReport],'yyyy',0,0))<>'') AND ((Orders.Order_received)>='2021-05-06' And (Orders.Order_received)<='2023-07-05'));


I suspect that it may not be possible, but I am not an advanced programmer!

Thanks for your input
Harris

 

[CJ_London]

have you tried doing two crosstabs? one for the years and one for the months, then in a third query link the two together on your test field?

Another way would be be to use a union query to union a query counting the yearly totals with a query counting monthly totals, then crosstab the union query. Just need to make sure the group by year and group by month are the same column

might be better to state your month as yyyy-mm to ensure you get the right order so your union query would give

2021
2021-01
2021-02
...
2022
2022-01

etc

 

[Harris@Z]

Thanks so much for a great idea.
I have created the union on the two original queries, but have hit a snag with the crosstab.
I do not know how to allow for two column headings (using the wizard which only allows one)
This code creates the crosstab for only the monthly part

TRANSFORM Count(tempQryTAY.[TheMonth]) AS CountMonth
SELECT tempQryTAY.[Test], Count(tempQryTAY.[TheMonth]) AS [TotalMonth]
FROM tempQryTAY
GROUP BY tempQryTAY.[Test]
PIVOT tempQryTAY.[Year]

Can you please assist?

Regards,
Harris

 

[CJ_London]

I have created the union on the two original queries, but have hit a snag with the crosstab.
I do not know how to allow for two column headings (using the wizard which only allows one)
This code creates the crosstab for only the monthly part

as I said, month and year need to be in the same column

simplistically

SELECT test, format(somedate,"yyyy") as Period, count(*) as C
FROM myTable
GROUP BY test, format(somedate,"yyyy")
UNION SELECT test, format(somedate,"yyyy-mm") as Period, count(*) as C
FROM myTable
GROUP BY test, format(somedate,"yyyy-mm")

then base your crosstab on
rowheading - test
columnheading - Period
value - sum (or first) of C

 

[Harris@Z]

Hi CJ,
That is a fantastic solution, thank you. Mostly, I learnt a lot and became a little more skilful!

Could I ask for one more stab at this- how do I sort so that it displays all year, then months
At present shows 2020 then 2020/01, 2020/02, 2020/03, to 2020/12, then 2021, 2021/01, 2021/02 etc.

Would be great to have it show as
2020, 2021, 2023, 2024 etc, then 2020/01, 2020/02, 2020/3 etc
Or better
2020, 2021, 2023, 2024 etc, then 2020/01, 2021/01, 2022/01, 2023/01 etc

Much thanks for your help, greatly appreciated.

 

[CJ_London]

try

SELECT * FROM
(SELECT test, format(somedate,"yyyy") as Period, count(*) as C. 1 as S
FROM myTable
GROUP BY test, format(somedate,"yyyy")
UNION SELECT test, format(somedate,"yyyy-mm") as Period, count(*) as C, 2 as S
FROM myTable
GROUP BY test, format(somedate,"mm-yyyy"))
ORDER BY S, Period

Note the change of the date format for months since you want all month 1, followed by all month 2, etc

 

[Harris@Z]

This is brilliant, never seen this before.
However does not translate to the cross-tab.
I tried different approaches to the cross-tab query of the union, but I am unable to display as required.
I assume this is possible but I am missing how to do this?

 

[CJ_London]

no idea what you might be missing as you have not provided what you are actually doing

 

[Harris@Z]

Oh, sorry.

This is the Union Statement
SELECT TestsAvailable.Test, Format$([DateReport],'yyyy/mm',0,0) AS Period, Count(TestsAvailable.test) AS C, 1 as S
FROM TestsAvailable RIGHT JOIN (((Companies INNER JOIN Orders ON (Companies.Company_ID = Orders.Company_ID) AND (Companies.Company_ID = Orders.Company_ID)) LEFT JOIN Samples_List ON Orders.Order_ID = Samples_List.Order_ID) LEFT JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) ON TestsAvailable.Test_ID = SampleTestedFor.TestedFor
GROUP BY TestsAvailable.Test, Format$([DateReport],'yyyy/mm',0,0), Companies.Company_ID, Orders.Order_received
HAVING (((TestsAvailable.Test) Is Not Null) AND ((Format$([DateReport],'yyyy/mm',0,0))<>"")
UNION SELECT TestsAvailable.Test, Format$([DateReport],'yyyy',0,0) AS Period, Count(TestsAvailable.test) AS C, 2 as S
FROM TestsAvailable RIGHT JOIN (((Companies INNER JOIN Orders ON (Companies.Company_ID = Orders.Company_ID) AND (Companies.Company_ID = Orders.Company_ID)) LEFT JOIN Samples_List ON Orders.Order_ID = Samples_List.Order_ID) LEFT JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) ON TestsAvailable.Test_ID = SampleTestedFor.TestedFor
GROUP BY TestsAvailable.Test, Format$([DateReport],'yyyy',0,0), Companies.Company_ID, Orders.Order_received
HAVING (((TestsAvailable.Test) Is Not Null) AND ((Format$([DateReport],'yyyy',0,0))<>"")
ORDER BY S, Period;

This is the Crosstab

TRANSFORM Count([UnionYearMonth].C) AS CountOfC
SELECT [UnionYearMonth].Test, Count([UnionYearMonth].C) AS [Total Of C]
FROM UnionYearMonth
GROUP BY [UnionYearMonth].Test, [UnionYearMonth].S, [UnionYearMonth].C
ORDER BY [UnionYearMonth].S, [UnionYearMonth].C
PIVOT [UnionYearMonth].Period;

I tried different versions of this: ORDER BY [UnionYearMonth].S, [UnionYearMonth].C

Thanks for your time and expertise

 

[CJ_London]

the union query is wrong because you are doing the month before the year. change the S order around - 2 for the month, 1 for the year

Don't know why you are including Companies.company_ID and Order_Received in your group by - you are not selecting it so you will get multiple rows for each test - no point because you are just going to count them again. In fact not sure why you are including those tables anyway since you otherwise do not reference them

Also not sure your about your crosstab which is grouping on C - which it shouldn't be

 

[Harris@Z]

Apologies, my fault for trying to only show the pertinent aspect - realise it would have caused major confusion.

The original code allows the selection of a company, all companies, and a date range.

The union is this:
SELECT TestsAvailable.Test, Format$([DateReport],'yyyy',0,0) AS Period, Count(TestsAvailable.test) AS C, 1 as S
FROM TestsAvailable RIGHT JOIN (((Companies INNER JOIN Orders ON (Companies.Company_ID = Orders.Company_ID) AND (Companies.Company_ID = Orders.Company_ID)) LEFT JOIN Samples_List ON Orders.Order_ID = Samples_List.Order_ID) LEFT JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) ON TestsAvailable.Test_ID = SampleTestedFor.TestedFor
GROUP BY TestsAvailable.Test, Format$([DateReport],'yyyy',0,0), Companies.Company_ID, Orders.Order_received
HAVING (((TestsAvailable.Test) Is Not Null) AND ((Format$([DateReport],'yyyy',0,0))<>"") AND ((Companies.Company_ID) Like '*') AND ((Orders.Order_received)>='2020-05-06' And (Orders.Order_received)<='2023-07-05'))
UNION SELECT TestsAvailable.Test, Format$([DateReport],'yyyy/mm',0,0) AS Period, Count(TestsAvailable.test) AS C, 2 as S
FROM TestsAvailable RIGHT JOIN (((Companies INNER JOIN Orders ON (Companies.Company_ID = Orders.Company_ID) AND (Companies.Company_ID = Orders.Company_ID)) LEFT JOIN Samples_List ON Orders.Order_ID = Samples_List.Order_ID) LEFT JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) ON TestsAvailable.Test_ID = SampleTestedFor.TestedFor
GROUP BY TestsAvailable.Test, Format$([DateReport],'yyyy/mm',0,0), Companies.Company_ID, Orders.Order_received
HAVING (((TestsAvailable.Test) Is Not Null) AND ((Format$([DateReport],'yyyy/mm',0,0))<>"") AND ((Companies.Company_ID) Like '*') AND ((Orders.Order_received)>='2020-05-06' And (Orders.Order_received)<='2023-07-05'))
ORDER BY S, Period;


The results of this Union qry:

And the Crosstab was this:
TRANSFORM Count([1UnionYearMonth].C) AS CountOfC
SELECT [1UnionYearMonth].Test, Count([1UnionYearMonth].C) AS [Total Of C]
FROM 1UnionYearMonth
GROUP BY [1UnionYearMonth].Test
PIVOT [1UnionYearMonth].Period;

However the order is not what I had hoped for, i.e.,
2020, 2020/01, 2020/02 ..... 2020/12, 2021, 2021/01 etc

 

[ebs17]

The only way you can influence the sorting of columns in a crosstab query is by using the naming convention. The column names are sorted, either alphanumerically (for strings) or numerically (for numbers). The ORDER BY clause doesn't matter at all.

As I see it, you need to go back to the original suggestion:

have you tried doing two crosstabs? one for the years and one for the months, then in a third query link the two together on your test field?

 

[CJ_London]

Having now got in front of a computer, I concur with @ebs17 - within the context of a crosstab, you cannot sort the columns on S

The only way to make it work is to add a character or characters to the beginning of the period to represent S. So if you want to go that route

I suggest forget your grouping within your union query. Simplify your base query and save it a as qryXT1 - note the change of ' to # around fixed date values (I'm assuming this is an access query not sql server)

SELECT TestsAvailable.Test,[DateReport]
FROM TestsAvailable RIGHT JOIN (((Companies INNER JOIN Orders ON (Companies.Company_ID = Orders.Company_ID) AND (Companies.Company_ID = Orders.Company_ID)) LEFT JOIN Samples_List ON Orders.Order_ID = Samples_List.Order_ID) LEFT JOIN SampleTestedFor ON Samples_List.Sample_ID = SampleTestedFor.Sample_ID) ON TestsAvailable.Test_ID = SampleTestedFor.TestedFor
WHERE TestsAvailable.Test Is Not Null AND [DateReport] is not null AND (Orders.Order_received>=#2020-05-06# And Orders.Order_received<=#2023-07-05#

Now your union query (called qryXT2) becomes

SELECT test, "  " & format(DateReport,"yyyy") as Period
FROM qryXT1
UNION
SELECT test, " " & format(DateReport,"yyyy-mm") as Period
FROM qryXT1

Note the double space for the year format and the single space for the month format to replace S

if you want the order to be 2020,2021....01-2020, 01-2021,....02-2020, 02-2021...etc change the month format to 'mm-yyyy'

and your Xtab becomes

TRANSFORM Count(*) AS CountOfC
SELECT Test
FROM qryXT2
GROUP BY Test
PIVOT Period

 

[Harris@Z]

Excellent proposals, and I am in wonder of both your skills at tackling this.
I am going to try both because I will learn a great deal from the process.
Thank you