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.
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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Most Valuable Expert award recognizes technology experts who passionately share their knowledge with the community, demonstrate the core values of this platform, and go the extra mile in all aspects of their contributions. This award is based off of nominations by EE users and experts. Multiple MVEs may be awarded each year.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.