I've been tasked with creating an Access database to handle tax delinquency data. I know exactly how I could use my Table1 (below) to create my FinalTable (also below) if I were working in Excel, but Access doesn't have the same countif and sumif functions, and as far as I know, doesn't allow you to reference other columns in a query, and now I'm stuck. Here's a simplified version of my dataset.
Table1
| FY | Parcel | Delinquent_1 | Delinquent_2 | Delinquent_Sum |
|---|---|---|---|---|
| 2020 | 1-19A | $500.00 | $500.00 | $1000.00 |
| 2020 | 1-20-2 | $0.00 | $500.00 | $500.00 |
| 2021 | 1-20-2 | $500.00 | $500.00 | $1000.00 |
| 2022 | 1-20-2 | $500.00 | $500.00 | $1000.00 |
| 2022 | 1-20-5 | $0.00 | $0.00 | $0.00 |
I need to write a query of some kind to get to the following result.
FinalTable
| Year | NumDelinqParcels | TotalDelinquent1 | TotalDelinquent2 | TotalUnpaid |
|---|---|---|---|---|
| 2020 | 2 | $500.00 | $1000.00 | $1500.00 |
| 2021 | 1 | $500.00 | $500.00 | $1000.00 |
| 2022 | 1 | $500.00 | $500.00 | $1000.00 |
To give more detail, NumDelinqParcels should display the number of parcels that have a Delinquent_Sum > $0.00 for the given year. TotalDelinquent1 will add up the Delinquent_1 column for the given year. TotalDelinquent2 will add up the Delinquent_2 column for the given year. TotalUnpaid will sum TotalDelinquent1 and TotalDelinquent2.
If I was working in Excel, my NumDelinqParcels could be populated with a countif statement, and my TotalDelinquent1 and TotalDelinquent2 could be populated with a sumif statement. I can't get these to work in Access.
For TotalDelinquent1 I can get as far as:
SubQuery1
SELECT Sum(Iif(FY = "2020" and Delinquent_Sum <> 0, Delinquent_1, 0)) from Table1
I could then work that into something like SELECT FY as "Year", Subquery1 as "TotalDelinquent1" etc. for my final table.
The problem with Subquery1 is that it's static. I can't get it to just look at the year in the first column and fill that in for the "FY =" portion. Maybe I'm going in the wrong direction.
Any advice is much appreciated!
Looks like a simple aggregate (GROUP BY) query to me.
Delinquent_Sum should not be a field in table, it should be calculated in query. If you want to dynamically filter by FY, use WHERE clause. Below demonstrates query with a popup input prompt for FY. I don't use popup prompts. I prefer to use VBA to build filter criteria and apply to form or report when opening.
SELECT FY, Count(*) AS NumDelinqParcels, Sum(Delinquent_1) AS TotalDelinq1,
Sum(Delinquent_2) AS TotalDelinq2, Sum(Delinquent_1 + Deliinquent_2) AS TotalUnpaid
FROM Table1
WHERE FY=[enter year]
GROUP BY FY;