Avatar of Pau Lo
Pau Lo
 asked on

SQL query assistance - splitting data into multiple rows

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. 
Microsoft OfficeMicrosoft Access

Avatar of undefined
Last Comment
Tom Farrar

8/22/2022 - Mon
Dale Fye

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Tom Farrar

Is the data you refer to captured in an Access Database, or..?  For instance, is it captured in a spreadsheet that is then imported into an Access Database?
Pau Lo

We have the data in both xlsx and in a table in the database.  So yes originally recorded (wrongly) in a a spreadsheet
Tom Farrar

Gotcha, sounds like the issue now needs to be fixed in the database.  I was just thinking the problem could most likely be fixed before the data was imported to the Access table using Excel's Power Query ETL functionality.  If the data continued to come in incorrectly, and Access did not have an adequate solution, you could consider fixing the problem before updating a table in Access.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Pau Lo

Yeah it can be addressed outside of Access I suppose. Is the power query add-in a freebie for it to be fully functional or does it require a license? How the data is gathered regardless of destination needs addressing but for now we just need to get a report in the correct format.
Tom Farrar

The Power Query functionality is embedded in Excel.  No license.  It is an add-in for early versions of Excel, but is embedded in later versions like 2016 and 365.  The real advantage of Power Query is the automation of a data import and ETL.  If you have a repetitive task bringing data in and cleansing and/or preparing it for other software or reporting, it is a great tool.