Share via

Microsoft Access Query: Reference Value from Previous Row To Use in a Calculation

Anonymous
2023-08-08T21:37:25+00:00

Hello,

I'm have a query that I'm trying to reference the previous row's value to calculate a percentage change. I can't figure it out. Here's my info:

Table: Transactions

| | | Transactions | | ID | Type | OpenDate | | --- | --- | --- | | 774 | S | 01/01/2023 | | 775 | S | 01/01/2023 | | 777 | R | 01/01/2023 | | 778 | S | 02/01/2023 | | 779 | S | 02/01/2023 | | 780 | S | 02/01/2023 | | 782 | R | 02/01/2023 | | 783 | S | 03/01/2023 | | 784 | R | 03/01/2023 | | 785 | S | 03/01/2023 | | 786 | R | 03/01/2023 | | 787 | R | 04/01/2023 | | 788 | S | 04/01/2023 | | 789 | S | 04/01/2023 |

Query:

SQL:

SELECT Format([OpenDate],"mm-yyyy") AS [Month], Count(Format([Type],"Standard")) AS Total, [PreviousTotal] AS Expr1, Format(([Total]-[PreviousTotal])/[PreviousTotal],"Percent") AS [%Change]

FROM Transactions

GROUP BY Format([OpenDate],"mm-yyyy");

| | | | Query1 | | Month | Total | PreviousTotal | %Change | | --- | --- | --- | --- | | 01-2023 | 3 | | | | 02-2023 | 4 | 3 | | | 03-2023 | 4 | 4 | | | 04-2023 | 3 | 4 | |

OpenDate Field: Formatted to Month and Year and Group it

Type Field: Count it and Format it to Standard so I can use it to calculate %Change

PreviousTotal: Should be the previous month's Total

%Change: ([Total]-[PreviousTotal])/([PreviousTotal])

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-08-15T07:16:15+00:00

    Let's take today 15-Aug-2023 as example.

    [OpenDate] = 15-Aug-2023

    Day([OpenDate]) = 15

    [OpenDate]-Day([OpenDate]) = 31-Jul-2023

    [OpenDate]-Day([OpenDate])+1 = 1-Aug-2023

    Regardless of the day of the month, [OpenDate]-Day([OpenDate])+1 always returns the first day of that month.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-08-14T21:37:31+00:00

    Thank you HansV, can you explain step 1?

    Step 1:

    SELECT [OpenDate]-Day([OpenDate])+1 AS TheMonth, Count(Transactions.Type) AS Total
    FROM Transactions
    GROUP BY [OpenDate]-Day([OpenDate])+1;

    Does is matter that [OpenDate] value is any day of the month? I just used the 1st as sample data...

    0 comments
  2. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-08-09T11:10:27+00:00

    Step 1: create a query named MonthlyTotal with SQL:

    SELECT [OpenDate]-Day([OpenDate])+1 AS TheMonth, Count(Transactions.Type) AS Total
    FROM Transactions
    GROUP BY [OpenDate]-Day([OpenDate])+1;

    Open this query in design view and apply a custom format such as mm-yyyy or mmm-yyyy to the TheMonth column.

    (I used TheMonth to avoid confusion with the built-in Month function).

    Step 2: create a query with SQL:

    SELECT TheMonth, Total, DLookUp("Total","MonthlyCount","TheMonth=#" & Format(DateAdd("m",-1,[TheMonth]),"yyyy-mm-dd") & "#") AS PreviousTotal, [Total]/[PreviousTotal]-1 AS [%Change]FROM MonthlyCount;

    Open this query in design view and apply Percent format to the %Change column.

    0 comments