Remove Duplicates - Leave One Dependant on Value

[SDowse]

Hi,

I have an access table with Direct Debit mandates. Three main headers - DD Ref, DD Start Date and DD Status.

There are duplicates in the DD Ref field where a customer has more than 1 mandate, but the DD Status is possibly different if there are more than one.

Essentially, I want a table with no duplicates in DD Ref where:
If no duplicate DD Ref - leave as is
If duplicate DD Ref, and one DD Status is Active - leave Active
If duplicate DD Ref, and both DD Status are the Same - keep newest DD Start Date

An ideas if / how this can be done?

Many thanks in advance.

 

[xenou]

This is a sql solution but not written in Access. It will need a little adaption but the logic should be clear from this demo.
Note that half of this is just setting up some test data.
In your case, you don't need test data as you have data to test already, but you will need to do these things:

• create some table to be your "Result" table,
• create threee queries to do the delete, insert, and update part,
• optionally, create a vba procedure you can call (e.g., from a button on a form), or otherwise create a macro - basically, something to to run the three queries in turn and show the result.

-- ----------
-- SETUP TEST
-- ---------

drop table if exists #DD, #Result

create table #DD (SeqNum int, DD_Ref varchar(10), DD_Status varchar(10), DD_Date datetime)
create table #Result (DD_Ref varchar(10), DD_Status varchar(10), DD_Date_Max datetime)

--PREPARE TEST DATA
insert into #DD values

(1, 'Ref1', 'Closed', '2020-01-10'),
(2, 'Ref1', 'Active', '2020-01-10'),
(3, 'Ref2', 'Closed', '2020-01-11'),
(4, 'Ref2', 'Closed', '2020-01-12'),
(5, 'Ref3', 'Active', '2020-01-01'),
(6, 'Ref3', 'Closed', '2020-01-01')

-- --------
-- RUN TEST
-- -------

-- 1) clear previous results
delete from #Result

-- 2) all Ref/Status pairs with most recent date selected
insert into #Result (DD_Ref, DD_Status, DD_Date_Max)
    select distinct DD_Ref, DD_Status, Max(DD_Date) as DD_Date_Max
    from #DD
    group by DD_Ref, DD_Status

--3) remove any case where status is not active and an active record exists
delete r from #Result r
    where r.DD_Status <> 'Active'
        and exists (select * from #Result r2 where r2.DD_Ref = r.DD_Ref and r2.DD_Status = 'Active')


-- View Result
select * from #Result

-- Expected:
-- Ref1, Active, 2020-01-10
-- Ref2, Closed, 2020-01-12
-- Ref3, Active, 2020-01-01

If I were brainstorming other ideas, you could optionally try to work from the other end
1) put all the records in your temp table
2) delete the ones that have a status that is not active where there exists a status that is active
3) delete the ones that have the same status and keep only the one with the highest date
that should do it I guess. Same number of queries but slightly different order of operations and actions to take.

In all cases I have not tried to deal with Null values but that may effect outcomes depending on how Nulls might be used in comparisons.

Note: and don't you just wish you could put comments in your SQL code in Access ... ?