I have a database that I can't control the table set up for as it comes from another system.
I have a SQL copy of this database that I write queries & reports from using Access. I have a query which works but I need to filter down further so I end up with just one line entry per person of outstanding reviews. I have the query filtering out those that are already completed (Did date Like **)
In many instances there are fields where there are duplicate values (i.e. Employee#, Claim# and Start Date) I want the query or report to show only the most current review date (for Emp#1 would be 1/1/23; for Emp#2 would be 18/1/23). I can easily do this task in excel but I need to be able to do this in access so it can be automated. Some of the claims may only have 1 review and not duplicate such as Emp#3
I have a SQL copy of this database that I write queries & reports from using Access. I have a query which works but I need to filter down further so I end up with just one line entry per person of outstanding reviews. I have the query filtering out those that are already completed (Did date Like **)
In many instances there are fields where there are duplicate values (i.e. Employee#, Claim# and Start Date) I want the query or report to show only the most current review date (for Emp#1 would be 1/1/23; for Emp#2 would be 18/1/23). I can easily do this task in excel but I need to be able to do this in access so it can be automated. Some of the claims may only have 1 review and not duplicate such as Emp#3
EMP# | CLAIM# | START DATE | REVIEW DATE | TO DO |
1 | 99 | 1/5/22 | 1/1/23 | Update benefits |
1 | 99 | 1/5/22 | 1/2/23 | Updated medical required |
1 | 99 | 1/5/22 | 5/4/23 | Confirm disability status |
1 | 99 | 1/5/22 | 6/5/23 | Post position |
2 | 105 | 12/12/22 | 18/1/23 | Updated medical required |
2 | 105 | 12/12/22 | 18/2/23 | PEER Review |
2 | 105 | 12/12/22 | 22/4/23 | Update benefits |
2 | 105 | 12/12/22 | 23/5/23 | Record of Employment |
2 | 105 | 12/12/22 | 4/8/23 | Return to work |
3 | 785 | 12/12/23 | 7/10/23 | Age 65, retired? |