Duplicate Records 1

[redaccess]

I have a table with an Accounts field, and a Date field.
I want to delete all duplicate accounts that don't have the most current date. For ex.

Accounts Date
01 01/01/01 (delete)
01 12/04/01
02 03/01/01 (delete)
02 12/03/01

Any suggestions?

 

[pmanivas]

Delete * from table1 A where A.account = ( select account from table1 B where B.date > A.date and A.account = b.account)

 

[AHHH]

I'm not too sure on this, but you can create a query that will delete records for you. Start a new query in design view and then pick "Delete" from the Query menu. You will then have to add criteria to the date fields so you don't delete the wrong information. Maybe someone else can help you better with adding the criteria. I hope this helps

 

[Dorian]

Did pmanivas's answer work for you? I've been trying to duplicate your problem and use it. Maybe I'm not implementing it right.

Anyway, I tried a multi-step process with the following in SQL view of two queries:

Query#1: (Query1)

SELECT Table.accounts, Max(Table1.dates) AS MaxOfdates
FROM Table
GROUP BY Table.accounts;

This isolates most current dates and is helpful if accounts is your index with no other primary key. Or you may want to remove the primary key temporarily if it's just an arbitrary thing like an autonumber.

Query#2: Make a new table with only your current dates' records, which you can rename to replace the original table with the duplicates:

SELECT Table.* INTO Table2
FROM Table, Query1
WHERE (((Table.accounts)=[query1].[accounts]) AND ((Table.dates)=[query1].[maxofdates]));

 

[redaccess]

I've tried implementing your solution Dor100 but in your first query I get an error saying that 'Table.accounts isn't part of an Aggregate Function'.
Pmanivas's answer did work, partly. It can't handle more than one duplicate record. If there's more than two different account numbers, an error occurs.

 

[jhall156]

DOR100's approach is right. But I think you have to use a delete query and the max query has to max the combination of the account and date (not fun) lika dis.

qryMostCurrentAccountDate-
SELECT accounttbl.account, MAX(accounttbl.account +
STR(YEAR(accounttbl.accountdate)) +
STR(MONTH(accounttbl.accountdate)) +
STR(DAY(accounttbl.accountdate)) AS CurrentAccountDate
FROM accounttbl
GROUP BY accounttbl.account

qryDeleteOldAccountDates-
DELETE * FROM accounttbl a
INNER JOIN
qryMostCurrenAccountDate b ON a.account =
b.account WHERE a.account +
STR(YEAR(a.accountdate)) +
STR(MONTH(a.accountdate)) +
STR(DAY(a.accountdate)) <> b.CurrentAccountDate

 

[jhall156]

oops forgot something in the where clause


qryMostCurrentAccountDate-
SELECT accounttbl.account, MAX(accounttbl.account +
STR(YEAR(accounttbl.accountdate)) +
STR(MONTH(accounttbl.accountdate)) +
STR(DAY(accounttbl.accountdate)) AS CurrentAccountDate
FROM accounttbl
GROUP BY accounttbl.account

qryDeleteOldAccountDates-
DELETE * FROM accounttbl a
INNER JOIN
qryMostCurrenAccountDate b ON a.account =
b.account WHERE a.account +
STR(YEAR(a.accountdate)) +
STR(MONTH(a.accountdate)) +
STR(DAY(a.accountdate)) <>
b.account + b.CurrentAccountDate

 

[Dor100]

Not sure why you'd get that message. I'm thinking maybe if you used the design grid instead of SQL view, it's easy to forget to click &quot;Group By&quot; for Accounts. It's working for me, but I wanted to try (most of) JHall156's approach because it would be more efficient. I've kept the &quot;max of&quot; approach that I started with, but tried it JHall156's way first without the table aliases a & b, and then with them like this:

SELECT Table1.accounts, Max(Table1.dates) AS MaxOfdates
FROM Table1
GROUP BY Table1.accounts;

And then:

DELETE a.*
FROM table1 a INNER JOIN query1 b ON a.accounts = b.accounts
WHERE (((a.dates)<>.[maxofdates]));

(By the way, I've since changed the table name from &quot;table&quot; to &quot;table1,&quot; but my first response above should say &quot;..max(table.dates)...&quot; instead of &quot;...max(table1.dates)...&quot

You see the records you're targeting in datasheet view, but unfortunately all I got was &quot;operation must use an upadatable query.&quot; My first approach is working, albeit ultimately taking more steps than JHall156 if successful. I'd like to know if you succeed with (all of) JHall156's way, because that looks like some pretty nifty stuff. Also, do you know if these table aliases are really necessary? That's something I've avoided in Access, so if anyone can shed some light I'd appreciate it. Good luck, Redaccess, and I'd sure like to know how you do from here.

 

[Dor100]

Oops #2, looks like I used my other handle instead of Dor100, but they're both me. Dorian is the older handle.

 

[redaccess]

Sweet!! jhall156's code worked except for the last line.

b.account + b.CurrentAccountDate

You don't need the b.account because that's adding the account number back onto the acct+date number, therefore giving two acct numbers.

Here's the correct code for the second query.
DELETE * FROM tbl_Overdraft1 a
INNER JOIN
qry_Example b ON a.odacct =
b.odacct WHERE a.odacct +
STR(YEAR(a.oddate)) +
STR(MONTH(a.oddate)) +
STR(DAY(a.oddate)) <>
b.Currentdate;

This code will delete numerous records with duplicate acct #'s that have dates previous to the most recent date.

Thank you jhall156, and Dorian.

 

[redaccess]

One last error,
When I View what the second query will delete, it shows all the correct records that should be deleted. But when I Run the query, I get an error saying &quot;Specify the table containing the records you want to delete&quot;.
Why would it show me the records that it's gonna delete, but then give me an error when I run it asking for the table?

 

[redaccess]

Never mind,
The second query has to be an updatable record and the code has to look like this,
DELETE a.* FROM tbl_Overdraft1 a
INNER JOIN
qry_Example b ON a.odacct =
b.odacct WHERE a.odacct +
STR(YEAR(a.oddate)) +
STR(MONTH(a.oddate)) +
STR(DAY(a.oddate)) <>
b.Currentdate;