Removing Single Duplicates (plus other things)

Unas_ · Apr 15, 2013

Hello, I have a little bit of experience in Access (i've built a few production databases) but I am having an issue that I cannot resolve. Here it goes....

I have a query that contains all transactions for a service. I am needing to filter this by first seeing if there are duplicate IDNumbers and remove the oldest by date. After that is done it needs to filter that list by date from today back 90 days and if PostCardSent date is not null.

I have created a query that allows me to add criteria for the past 90 days (<Date()-90) and PostCardSent (Is Null) but I cannot figure out the first part.

The reason for this is that we want to be able to find if a person has not reordered their item from within 90 days of today but if they have then ignore or remove the that transaction from this query.

Does this make sense? Thank you for all your help!

CJ_London · Apr 15, 2013

use the query wizard to create a find duplicates query

Assuming your table is called YourTbl and the date field is called TDate then in the date field put the criteria.

(SELECT Min(TDate) FROm YourTbl AS Tmp WHERE IDNumber= YourTbl.IDNumber)

This will limit your recordset to the earliest record for each IDNumber. After that, do what you need re 90 days etc

Unas_ · Apr 15, 2013

Hello, I tried this and it has given me a syntax error. Could you assist? I have find duplicates query with the following fields (MRN, Patient_Last_Name, Patient_First_Name, PhoneNumber, StreetAddress, City, State, ZipCode, MixedDate, PostCardSentDate).

Thank you.

Unas_ · Apr 15, 2013

Hey, sorry I tried it several times and finally got it to accept it but it pops up with a text box asking for the MRN number. Any clues?

CJ_London · Apr 15, 2013

This implies that MRN is not in your table - I would check the spelling.

To help otherwise your will need to pst the sql to your query and full details of the error (error number and description)

Unas_ · Apr 16, 2013

Hello, I got it to work and it displays duplicate MRN's next to each other but how do I limit the list by removing the oldest if there is a duplicate and then displaying the last 90 days?

Here is my SQL criteria

This is under the MRN 'Or' heading:
In (SELECT [MRN] FROM [PostCardSent] As Tmp GROUP BY [MRN] HAVING Count(*)>1 )

This is under the MixedDate 'Criteria' heading:
(SELECT Min(MixedDate) FROM [DuplicatePostCardSent] AS Tmp WHERE ([PostCardSent].[MRN])=[PostCardSent].[MRN])

Let me know if you need any more information.

Thanks again!

MSAccessRookie · Apr 16, 2013

Unas_ said:
Hello, I got it to work and it displays duplicate MRN's next to each other but how do I limit the list by removing the oldest if there is a duplicate and then displaying the last 90 days?

Here is my SQL criteria

This is under the MRN 'Or' heading:
In (SELECT [MRN] FROM [PostCardSent] As Tmp GROUP BY [MRN] HAVING Count(*)>1 )

This is under the MixedDate 'Criteria' heading:
(SELECT Min(MixedDate) FROM [DuplicatePostCardSent] AS Tmp WHERE ([PostCardSent].[MRN])=[PostCardSent].[MRN])

Let me know if you need any more information.

Thanks again!

I am not sure that I fully understand your requirements, but I wonder whether the Code that is marked in RED will do what you want it to.

Please address the following two observations:

Since both sides of the Equation have exactly the same Field to compare, it will be true every time that one MRN Value matches another MRN Value. If all MRN Value are unique, then this would not be an issue.
In addition, If an MRN Value can be NULL, then all such values would match as well. If an MRN Value cannot be NULL, then this would not be an issue.

Unas_ · Apr 16, 2013

MSAccessRookie said:
I am not sure that I fully understand your requirements, but I wonder whether the Code that is marked in RED will do what you want it to.

Please address the following two observations:

Since both sides of the Equation have exactly the same Field to compare, it will be true every time that one MRN Value matches another MRN Value. If all MRN Value are unique, then this would not be an issue.

In addition, If an MRN Value can be NULL, then all such values would match as well. If an MRN Value cannot be NULL, then this would not be an issue.

You are correct. The MRN value is the primary key in PatientData table and cannot be null.

CJ_London · Apr 17, 2013

I think we have got off track here, so need to clarify your definition of duplicate. From your original post you said duplicate IDNumbers. It looks like this is your MRN field.

You have two date fields in your fields, I shall assume MixedDate is the field which will indicate the the earliest record to be deleted.

I said to use the query wizard to create a duplicates query which should have looked like this (note this is untested and once viewed through the query builder will add numerous brackets and table names):

Code:

SELECT MRN, MixedDate
FROM PostCardSent
WHERE MRN IN (SELECT MRN FROM Postcardsent as tmp
GROUP BY MRN
HAVING Count(*)>1)
ORDER BY MRN;

I then said to add a criteria to the date field to limit the records returned to the earliest one so your revised query would look like this

Code:

SELECT MRN, MixedDate
FROM PostCardSent
WHERE MRN IN (SELECT MRN FROM Postcardsent as tmp
GROUP BY MRN
HAVING Count(*)>1)
[COLOR=red]AND[/COLOR]
[COLOR=red]MixedDate=(SELECT Min(MixedDate) FROM PostCardSent as Tmp WHERE MRN=postcardset.MRN)[/COLOR]
ORDER BY MRN;

After that, it depends on what you want to do

seeing if there are duplicate IDNumbers and remove the oldest by date

- if you want to delete the records then the code becomes

Code:

[COLOR=red]DELETE *[/COLOR]
FROM PostCardSent
WHERE MRN IN (SELECT MRN FROM Postcardsent as tmp
GROUP BY MRN
HAVING Count(*)>1)
AND
MixedDate=(SELECT Min(MixedDate) FROM PostCardSent as Tmp WHERE MRN=postcardset.MRN);

If you can confirm you have the above working, we can then look at the next part which needs to be clarified

Unas_ · Apr 17, 2013

CJ_London said:
I think we have got off track here, so need to clarify your definition of duplicate. From your original post you said duplicate IDNumbers. It looks like this is your MRN field.

You have two date fields in your fields, I shall assume MixedDate is the field which will indicate the the earliest record to be deleted.

I said to use the query wizard to create a duplicates query which should have looked like this (note this is untested and once viewed through the query builder will add numerous brackets and table names):

Code:

SELECT MRN, MixedDate FROM PostCardSent WHERE MRN IN (SELECT MRN FROM Postcardsent as tmp GROUP BY MRN HAVING Count(*)>1) ORDER BY MRN;

I then said to add a criteria to the date field to limit the records returned to the earliest one so your revised query would look like this

Code:

SELECT MRN, MixedDate FROM PostCardSent WHERE MRN IN (SELECT MRN FROM Postcardsent as tmp GROUP BY MRN HAVING Count(*)>1) [COLOR=red]AND[/COLOR] [COLOR=red]MixedDate=(SELECT Min(MixedDate) FROM PostCardSent as Tmp WHERE MRN=postcardset.MRN)[/COLOR] ORDER BY MRN;

After that, it depends on what you want to do

- if you want to delete the records then the code becomes

Code:

[COLOR=red]DELETE *[/COLOR] FROM PostCardSent WHERE MRN IN (SELECT MRN FROM Postcardsent as tmp GROUP BY MRN HAVING Count(*)>1) AND MixedDate=(SELECT Min(MixedDate) FROM PostCardSent as Tmp WHERE MRN=postcardset.MRN);

If you can confirm you have the above working, we can then look at the next part which needs to be clarified

Hello, thanks again for your help but let me clarify what I am needing (and again, thank you for the help!).

I am needing a list of all my "Requests" but with the following criteria applied:
1. I need the entire list but only the ones that have a "MixedDate" of 90 days or older.
2. I am also needing to exclude "Requests" that have a "MixedDate" that is sooner than those in the "90 day or later" list.

Example: I have Request in my list that shows a Mixed Date of 12/21/2012 but my current list doesn't look and see if this MRN has a Mixed Date since that 12-21-2012.

Does that make sense? I will see if I can get the above working.

Thanks again for you help, you obvisiously know a heck of a lot more about Access than I Do.

CJ_London · Apr 17, 2013

I am needing a list of all my "Requests" but with the following criteria applied:
1. I need the entire list but only the ones that have a "MixedDate" of 90 days or older.
2. I am also needing to exclude "Requests" that have a "MixedDate" that is sooner than those in the "90 day or later" list.

Sorry, Can you try a different explanation of what you want - the above does not make sense to me. Ideally with several examples of what to include/exclude. Surround with code tags (#) in advanced reply to preserve formatting

e.g.

Code:

MRN   MixedDate    Today=31/3/13 - 90 days ago=1/1/13
1       1/1/12          include
1       1/2/13          exclude
2       1/3/12          include
2       1/11/12        include
3 etc
3
3

Unas_ · Apr 17, 2013

CJ_London said:
Sorry, Can you try a different explanation of what you want - the above does not make sense to me. Ideally with several examples of what to include/exclude. Surround with code tags (#) in advanced reply to preserve formatting

e.g.

Code:

MRN MixedDate Today=31/3/13 - 90 days ago=1/1/13 1 1/1/12 include 1 1/2/13 exclude 2 1/3/12 include 2 1/11/12 include 3 etc 3 3

Here are some examples:

Code:

MRN   MixedDate    Today=31/3/13 - 90 days ago=1/1/13
1       1/1/12          exclude
1       1/2/13          include
2       1/3/12          exclude
2       1/11/12         include
3       02/20/13       include
3       02/19/13       exlcude
3       01/19/13       exclude

MRN is from Table SCIT_Patient_Master_Table"
PatientName is from Table "SCIT_Patient_Master_Table"
Phone# is From Table "Request"
City is From Table "Request"
State is From Table "Request"
MixedDate is from Table "MixedState"
PostCarSentDate is From Table "Request"

Does this help. I would post images but I cannot since I am new.

CJ_London · Apr 17, 2013

OK so to summarise, you simply want to show the latest record - as highlighted in red - the 90 days does not seem to come into it since MRN 2 is included and is before the 90 days whilst MRN 1 & 3 are included and is after the 90 days - Please can you confirm

Code:

MRN   MixedDate    Today=31/3/13 - 90 days ago=1/1/13
1       1/1/12          exclude
[COLOR=red]1 [/COLOR][COLOR=red]      1/2/13          include
[/COLOR]2       1/3/12          exclude
[COLOR=red]2       1/11/12         include
3       02/20/13       include
[/COLOR]3       02/19/13       exlcude
3       01/19/13       exclude

Unas_ · Apr 17, 2013

CJ_London said:
OK so to summarise, you simply want to show the latest record - as highlighted in red - the 90 days does not seem to come into it since MRN 2 is included and is before the 90 days whilst MRN 1 & 3 are included and is after the 90 days - Please can you confirm

Code:

MRN MixedDate Today=31/3/13 - 90 days ago=1/1/13 1 1/1/12 exclude [COLOR=red]1 [/COLOR][COLOR=red] 1/2/13 include [/COLOR]2 1/3/12 exclude [COLOR=red]2 1/11/12 include 3 02/20/13 include [/COLOR]3 02/19/13 exlcude 3 01/19/13 exclude

Yes and No, basically everytime we run the query we want to first exclude any request (with a older MixedDate) with the same MRN from the list and then exclude all requests from the last 90 days.

Does that make sense? The above is an example of the first list and then we would like to filter by requests past the 90 day threshold.

CJ_London · Apr 17, 2013

Ok - so you are doing two queries, the first as per the previous post and the second will produce the following result. Is this correct?

Code:

MRN   MixedDate    Today=31/3/13 - 90 days ago=1/1/13
                           1st query      2nd query
1       1/1/12          exclude        exclude
[COLOR=red]1 [/COLOR][COLOR=red]      1/2/13          include         exclude[/COLOR]
2       1/3/12          exclude        exclude
[COLOR=red]2       1/11/12         include        include[/COLOR]
[COLOR=red]3       02/20/13       include         exclude
[/COLOR]3       02/19/13       exlcude        exclude
3       01/19/13       exclude        exclude

Is there any reason this has to be done in two queries - because it can easily be done in one.

Unas_ · Apr 17, 2013

CJ_London said:
Ok - so you are doing two queries, the first as per the previous post and the second will produce the following result. Is this correct?

Code:

MRN MixedDate Today=31/3/13 - 90 days ago=1/1/13 1st query 2nd query 1 1/1/12 exclude exclude [COLOR=red]1 [/COLOR][COLOR=red] 1/2/13 include exclude[/COLOR] 2 1/3/12 exclude exclude [COLOR=red]2 1/11/12 include include[/COLOR] [COLOR=red]3 02/20/13 include exclude [/COLOR]3 02/19/13 exlcude exclude 3 01/19/13 exclude exclude

Is there any reason this has to be done in two queries - because it can easily be done in one.

Yes this is can all be done in one query. And yes the above is correct.

CJ_London · Apr 17, 2013

Making progress, but a couple more questions before providing a solution.

Previous queries were limiting what they returned to MRNs that had more than one record - is this correct? i.e. you do not want to return MRN's where there is only one record (per mrn=5 below)?

Also just to confirm, if you have more than one record which is older than the 90 days date, you want the latest one of those - per below (changed dates to american format)

Code:

MRN   MixedDate    Today=03/31/13 - 90 days ago=01/01/13
                           1st query      2nd query
1       01/01/12          exclude        exclude
1       02/01/13          include         exclude
2       03/01/12          exclude        exclude
2       11/01/12         include        include
3       02/20/13       include         exclude
3       02/19/13       exlcude        exclude
3       01/19/13       exclude        exclude
[COLOR=red]4       02/17/13       exclude        exclude[/COLOR]
[COLOR=red]5       12/25/12       include         exclude[/COLOR]
[COLOR=red]6       12/20/12       include         include[/COLOR]
[COLOR=red]6       06/15/12       include         exclude[/COLOR]

Unas_ · Apr 17, 2013

CJ_London said:
Making progress, but a couple more questions before providing a solution.

Previous queries were limiting what they returned to MRNs that had more than one record - is this correct? i.e. you do not want to return MRN's where there is only one record (per mrn=5 below)?

Also just to confirm, if you have more than one record which is older than the 90 days date, you want the latest one of those - per below (changed dates to american format)

Code:

MRN MixedDate Today=03/31/13 - 90 days ago=01/01/13 1st query 2nd query 1 01/01/12 exclude exclude 1 02/01/13 include exclude 2 03/01/12 exclude exclude 2 11/01/12 include include 3 02/20/13 include exclude 3 02/19/13 exlcude exclude 3 01/19/13 exclude exclude [COLOR=red]4 02/17/13 exclude exclude[/COLOR] [COLOR=red]5 12/25/12 include exclude[/COLOR] [COLOR=red]6 12/20/12 include include[/COLOR] [COLOR=red]6 06/15/12 include exclude[/COLOR]

No, I do want to return mrn's if there is only one record (basically the newest MixedDate (minus the 90days)

Your second question is correct. The Newest MixedDate should be returned.

There is one more caveat, if there is a date in the PostCardSentDate then do not display those results but that should just be a "Is Null" criteria in that field.

Thanks again!

CJ_London · Apr 17, 2013

OK - the solution should be this:

Code:

SELECT MRN, MixedDate
FROM PostCardSent
WHERE MixedDate=(SELECT Max(MixedDate) FROM PostCardSent as Tmp WHERE MRN=postcardset.MRN AND MixedDate<(Date()-90)) AND PostCardSentDate is Null 
ORDER BY MRN;

I think this will also meet your caveat

Unas_ · Apr 17, 2013

CJ_London said:
OK - the solution should be this:

Code:

SELECT MRN, MixedDate FROM PostCardSent WHERE MixedDate=(SELECT Max(MixedDate) FROM PostCardSent as Tmp WHERE MRN=postcardset.MRN AND MixedDate<(Date()-90)) AND PostCardSentDate is Null ORDER BY MRN;

I think this will also meet your caveat

THANK YOU THANK YOU THANK YOU!!!!!!!!! You're a genius!

Removing Single Duplicates (plus other things)

Registered User.

Super Moderator

Registered User.

Registered User.

Super Moderator

Registered User.

AWF VIP

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Similar threads

Users who are viewing this thread