Filtering/removing duplicates (1 Viewer)

[LoriR16]

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

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?

 

[Sarah.M]

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

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?

Welcome to AWF
Try to use Query Wizared

 

[theDBguy]

Not entirely sure but have also tried using a Totals query?

 

[Pat Hartman]

Welcome.

I have a database that I can't control the table set up for as it comes from another system.

If you are linking to the tables, that is true. If you are importing the data, then the best option is to normalize the data on the way in.

The data in your visual isn't duplicated. That is a normal view for a table that isn't normalized.

To select the most recent task, there are two options. You can use nested queries or you can use a subquery. One query finds the minimum ReviewDate for each Emp/Claim/StartDate and then the subselect retrieves the task.

This assumes that what you mean by "only the most current review date" is the minimum date in the list. This means that each day, you load new data and old data at some point drops off. Otherwise, you need a better definition of how to select the date you want.

I would think that the 1/2/23 date would be the one you want because that is the last date less than today so please clarify.

 

[LoriR16]

Welcome.

If you are linking to the tables, that is true. If you are importing the data, then the best option is to normalize the data on the way in.

The data in your visual isn't duplicated. That is a normal view for a table that isn't normalized.

To select the most recent task, there are two options. You can use nested queries or you can use a subquery. One query finds the minimum ReviewDate for each Emp/Claim/StartDate and then the subselect retrieves the task.

This assumes that what you mean by "only the most current review date" is the minimum date in the list. This means that each day, you load new data and old data at some point drops off. Otherwise, you need a better definition of how to select the date you want.

I would think that the 1/2/23 date would be the one you want because that is the last date less than today so please clarify.

Thank you for your response.

In my data I would want the minimum date in the list, so for EMP1 that would be 1/1/23 and for EMP2 that would be 18/1/23. Eventually they would fall off the report as they are completed.

 

[plog]

I want the query or report to show only the most current review date...
In my data I would want the minimum date in the list

Those are two different things. Even worse 'most current' is ambigous and can mean two things in and of itself. In all cases however, you simply need an aggregate query:

SQL Aggregate Functions - Microsoft Support

support.microsoft.com

I will give you the SQL for 'the minimum review date by employee:

SELECT [EMP#], MIN([REVIEW DATE] AS EarliestReviewDate
FROM YourTableName
GROUP BY [EMP#])

 

[Pat Hartman]

OK, that's part the solution. I think you need Claim# as well and possibly StartDate. Save that query and create a new query that joins to it on Whatever the group by fields are, plus the minReviewDT. Then select whatever else you want about the task.