I've have a data set that I've been really struggling to summarize and create a report that is easy to understand. I basically have two tables, one is Customer which has all the customer details and the other is the "Activities" which stores the activities done on their account (i.e notes on their account, emails and reminders that have been sent). They're linked on the Customer ID field. I'm working on a query to identify if the system has sent them two reminders and the date that the most recent reminders were sent.
Below is an example of the data. I added the two custom columns "60 Day Sent" and "30 Day Sent" to narrow down only the reminder emails sent by the system.
| Customer ID | Subject | Date Sent | 60 Day Sent | 30 Day Sent |
| C243334 | 60-day notice to renew your course: C243334 | 2022-03-05 | TRUE | FALSE |
| C243334 | Course will expire in 30 days: C243334 | 2022-04-05 | FALSE | TRUE |
| C243334 | 60-day notice to renew your course: C243334 | 2021-03-05 | TRUE | FALSE |
| C243334 | Course will expire in 30 days: C243334 | 2021-04-05 | FALSE | TRUE |
| C89098 | 60-day notice to renew your course: C89098 | 2022-09-04 | TRUE | FALSE |
| C3454 | 60-day notice to renew your course: C3454 | 2022-08-03 | TRUE | FALSE |
| C3454 | Course will expire in 30 days C3454 | 2022-09-03 | FALSE | TRUE |
| C775592 | Course will expire in 30 days C775592 | 2022-07-15 | FALSE | TRUE |
| C09348 | 60-day notice to renew your course: C09348 | 2021-06-14 | TRUE | FALSE |
| C09348 | 60-day notice to renew your course: C09348 | 2022-06-14 | TRUE | FALSE |
Where I'm stuck is creating a table or report that summarises if we've sent reminders and the date of the most recent reminder of each type. For example for customers C243334 and C09348, I only want to see the reminders we sent them in 2022 since those are the most recent. If we haven't sent anything the value can either be No or Blank. This would be my desired result:
| Customer ID | 60 Day Reminder | Date Sent | 30 Day Reminder | Date Sent |
| C243334 | Yes | 2022-03-05 | Yes | 2022-04-05 |
| C89098 | Yes | 2022-09-04 | | |
| C3454 | Yes | 2022-08-03 | Yes | 2022-09-03 |
| C775592 | | | Yes | 2022-07-15 |
| C09348 | Yes | 2022-06-14 | | |
