Quarterly Summary Query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Quarterly Summary Query

Chuck Corvec

3 months ago

So just to be up front, I am old and I have a serious case of brain fog today. Here is the scenario. I need to create a report that shows a count of three different things for all yearly quarters from Q2 2025 to Q1 2025. I will attach a screen shot of what it should look like that I created in Excel. This example is for cosmetics only. There are no formulas

The first column is Inquiries. This comes from a table where every time a client is interacted with it is referred to and an inquiry and a note is entered in to the table. I created an aggregate query that shows one quarter by using between in the criteria. In this one I grouped by date. I don't want that I want to group by quarter and it needs to show every quarter whether there is anything to count or not

	Chuck Corvec OP @Reply 3 months ago

	Chuck Corvec OP @Reply 3 months ago
The other two columns are a bit more complex but I thought I would take one step at a time

	Chuck Corvec OP @Reply 3 months ago
Ok here are two screen shots of what I have so far

	Chuck Corvec OP @Reply 3 months ago

	Chuck Corvec OP @Reply 3 months ago

	Chuck Corvec OP @Reply 3 months ago
The only left to fix on this query is to include all of the quarters that have a count of 0

	Alex Lewis @Reply 3 months ago
If you want to show all quarters, you want to make your query a right outer join. Right outer join says “Show ALL records from ClientInquiryNote_tbl and ONLY the records from Client_tbl where the joined fields are equal.” Click on the line relating the two tables and option 3 is a right outer join.

Kevin Robertson @Reply

3 months ago

Try using a Union All Query.
Go to SQL View and paste this in making any changes to Table Names / Field Names as necessary.

Details

SELECT

    Y.Y,

    1 AS Q,

    (

        SELECT

            Count(*)

        FROM

            NoteT AS N

        WHERE

            Year(N.NoteDate) = Y.Y

            AND DatePart("q", N.NoteDate) = 1

    ) AS ClientCount

FROM

    (

        SELECT DISTINCT

            Year(NoteDate) AS Y

        FROM

            NoteT

    ) AS Y

UNION ALL

SELECT

    Y.Y,

    2 AS Q,

    (

        SELECT

            Count(*)

        FROM

            NoteT AS N

        WHERE

            Year(N.NoteDate) = Y.Y

            AND DatePart("q", N.NoteDate) = 2

    ) AS ClientCount

FROM

    (

        SELECT DISTINCT

            Year(NoteDate) AS Y

        FROM

            NoteT

    ) AS Y

UNION ALL

SELECT

    Y.Y,

    3 AS Q,

    (

        SELECT

            Count(*)

        FROM

            NoteT AS N

        WHERE

            Year(N.NoteDate) = Y.Y

            AND DatePart("q", N.NoteDate) = 3

    ) AS ClientCount

FROM

    (

        SELECT DISTINCT

            Year(NoteDate) AS Y

        FROM

            NoteT

    ) AS Y

UNION ALL

SELECT

    Y.Y,

    4 AS Q,

    (

        SELECT

            Count(*)

        FROM

            NoteT AS N

        WHERE

            Year(N.NoteDate) = Y.Y

            AND DatePart("q", N.NoteDate) = 4

    ) AS ClientCount

FROM

    (

        SELECT DISTINCT

            Year(NoteDate) AS Y

        FROM

            NoteT

    ) AS Y

ORDER BY

    Y,

    Q;

	Kevin Robertson @Reply 3 months ago

	Chuck Corvec OP @Reply 3 months ago
Hi Alex That made no change. think it is the nature of the data

Chuck Corvec OP @Reply

3 months ago

Hi Kevin

So I reviewed your code and I have a question or two
I see where you are defining Q1, Q2, Q3 and Q4

What t I am not seeing is how you are only doing 2025 and 2026

The specific needs of this report are

2025 Q2, Q3 and Q4
2026 Q1,Q2, Q3 and Q4
2027 Q1,Q2, Q3 and Q4
2028 Q1

As I can not figure out how your code only does two years, I can not figure out to go to 3 years. Also in a perfect world I would like to ignore Q1 in 2025 as this project started in Q2 of 2025

	Kevin Robertson @Reply 3 months ago
That is from the sample data I added to the Table. The year comes from the Date field.

	Kevin Robertson @Reply 3 months ago
I added a record for June 2027 - All 4 Quarters are showing

Chuck Corvec OP @Reply

3 months ago

Ok so I think I see what is happening
None of my records had a date in 2027 or 2028
As soon as I changed 2 records with dates of 2027 and 2028 I see all quarters for all 4 years. Not quite what I need

There will be no dates entered in for 2027 or 2027 Until the first day of those years
But I need all of 2027 to show up with 0 as the count and Q1 of 2028 to show 0 as the count until we get to the point where the day is actually going to be entered
Additionally I still would like to eliminate Q1 of 2025 as the program started in Q2

Is this even possible?

	Chuck Corvec OP @Reply 3 months ago
2027 or 2028

	Chuck Corvec OP @Reply 3 months ago
Kevin and Alex Got it. And it is working perfect. Thanks

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View