Is there any way you can think for an SQL query in an Access database could help solve the following dilemma I am stuck with.
I have a table of data, which represents applications and subsequent payments over 3 dates per applicant. The team who collected the data was supposed to collect the data in such a way, that if an applicant received more than 1 payment during a specific calendar month, each payment should have been recorded on a separate record/row. Each applicant is only eligible for a maximum of 3 payments, some have received 1 payment, some have received 2 payments, and some have received 3 payments.
However, instead of collecting payments on a unique record per payment, they have captured the data as 3 payment date fields per applicant/record, e.g. payment date 1, payment date 2, payment date 3.
I subsequently need to produce a report that has 1 row per payment for any payments made during a particular calendar month (in this case March 2021). Payment dates for the whole table actually range between August 2020 – 14/05/2021. So payment 1, 2 or 3 could have any date value between that date range. What I need is a way to essentially produce a report with the following:
If only 1 value in payment date 1, payment date 2, or payment date 3 has a value during March 2021, then just return the 1 row for that record in the query results (the unique ID is called applicant ID). The query should return all fields associated with the record.
If 2 values in payment date 1, payment date 2, or payment date 3 represent a value during March 2021, then return 2 rows for that applicant ID in the report. The query should produce all fields associated with the record.
If 3 values in payment date 1, payment date 2 or payment date 3 have a value during March 2021, then return 3 rows for that applicant ID. The query should produce all fields associated with the record.
From there I can then do some basically manipulation of certain fields to get the report in the format we need for management purposes.