Query: Iff Cell = Cell below same column, then add "Delete"

[qzst66]

Hi, I am new to access. I have a table with duplicate data, i.e. column A has all the purchase order numbers, column B all the dates. I have sorted the table by purchase orders ascending and dates ascending. Now this table has duplicate records. Goal is to have only unique purchase order numbers with the latest date.

I would like to do a query, that add a column C to my table with the following formula:
If Cell1 of row 1 = Cell2 of row 2 in column A, then add a word "Delete", otherwise "Keep".



Output should be like this:

Column A Column B Column C
45 11/28/11 Delete
45 11/30/11 Keep
46 etc...

I have been trying with the IFF function, copy the formula from Excel to Access, but it does not work.
Please help.

[June7]

Can't do with IIf unless you build a custom VBA function that uses the IIf conditional and then updates table.

Google: access sql delete duplicate rows

To do with queries, use DELETE WHERE NOT EXISTS. Review this tutorial http://www.techonthenet.com/sql/delete.php

Do a Totals query that groups on Purchase order number and returns Max(OrderDate). Use this query as the criteria for the EXISTS clause subquery.

Here is another approach http://support.microsoft.com/kb/139444
and more help http://stackoverflow.com/questions/3...licate-records

Make sure you really want to delete http://office.microsoft.com/en-us/ac...001034558.aspx

[alansidman]

Crosspost: http://www.access-programmers.co.uk/...d.php?t=218830