troubleshooting Question

SQL query assistance - splitting data into multiple rows

Avatar of Pau Lo
Pau Lo asked on
Microsoft OfficeMicrosoft Access
6 Comments1 Solution17 ViewsLast Modified:
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. 
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros