In MS Access 365 I have 2 tables and I want to count the occurrence of a year from the first table in part of a string of the second table, purely with SQL (so far I used VBA, but I want to simplify).
The first table (tDistinctYears) contains all the years, in which one of our members paid:
| ID | PaymentYear |
|---|---|
| 1 | 2015 |
| 2 | 2016 |
| 3 | 2017 |
| 3 | 2018 |
| 4 | 2019 |
| 5 | 2020 |
| 6 | 2021 |
| 7 | 2022 |
The second table (tPayments) has all payments from members with one column containing a membership number and the other one containing payment years. Sometimes a member pays for one year, sometime for several years. The table therefore looks like that:
| MembershipNr | YearPayment |
|---|---|
| 11 | 2016 |
| 11 | 2017 |
| 11 | 2018 |
| 26 | 2017 |
| 26 | 2018;2019 |
| 26 | 2020;2021;2022 |
| 38 | 2016 |
| 38 | 2017 |
| 38 | 2018;2019;2020;2021 |
I want a query which tells me how many members paid in which year:
| PaymentYear | Count |
|---|---|
| 2015 | 0 |
| 2016 | 2 |
| 2017 | 3 |
| 2018 | 3 |
| 2019 | 2 |
| 2020 | 2 |
| 2021 | 2 |
I used the following SQL query, which I found using various answers on stackoverflow:
SELECT tDistinctYears.PaymentYear, (COUNT(tPayments.YearPayment)) AS [Count]
FROM tDistinctYears
LEFT JOIN tPayments ON tDistinctYears.PaymentYear like "*" & tPayments.YearPayment & "*"
WHERE (tDistinctYears.PaymentYear > 0 AND tDistinctYears.PaymentYear <= YEAR(NOW()))
GROUP BY tDistinctYears.PaymentYear;
But what I get is this:
| PaymentYear | Count |
|---|---|
| 2015 | 0 |
| 2016 | 2 |
| 2017 | 3 |
| 2018 | 1 |
| 2019 | 0 |
| 2020 | 0 |
| 2021 | 0 |
It seems as if the above query does not use the “like” expression in the JOIN ON section.
Can someone help me, please?
I think you are close just alter column in where condition tPayments.YearPayment should be first and tDistinctYears.PaymentYear should be inside like operator.
SELECT tDistinctYears.PaymentYear, (COUNT(tPayments.YearPayment)) AS [Count]
FROM tDistinctYears
LEFT JOIN tPayments ON tPayments.YearPayment like "*" &
tDistinctYears.PaymentYear
& "*" WHERE (tDistinctYears.PaymentYear > 0 AND tDistinctYears.PaymentYear <=
YEAR(NOW()))
GROUP BY tDistinctYears.PaymentYear;