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.
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
Last Comment
Tom Farrar
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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?
ASKER
We have the data in both xlsx and in a table in the database. So yes originally recorded (wrongly) in a a spreadsheet
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.
ASKER
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.
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.