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

6 hours 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 6 hours ago

	Chuck Corvec OP @Reply 6 hours 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 5 hours ago
Ok here are two screen shots of what I have so far

	Chuck Corvec OP @Reply 5 hours ago

	Chuck Corvec OP @Reply 5 hours ago

	Chuck Corvec OP @Reply 5 hours 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 4 hours 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

4 hours 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 4 hours ago

Next Unseen

New Feature: Comment Live View