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.

 

[Alansidman]

Look here for a tutorial on Select Distinct in SQL and then once you have the results, change the select query to a make table query. Or create a new query using the Select Distinct query as your record source and do a make table query. Now you can delete the original table and have a table with only unique IDs.

http://www.w3schools.com/sql/sql_distinct.asp

By the way, Access has no cells. That is a spreadsheet term. Access has fields (columns) and records (rows). It is best to start using the appropriate terms when asking for help so as not to confuse those you are asking.

 

[qzst66]

Look here for a tutorial on Select Distinct in SQL and then once you have the results, change the select query to a make table query. Or create a new query using the Select Distinct query as your record source and do a make table query. Now you can delete the original table and have a table with only unique IDs.
.

Thank you for the advise but SELECT DISTINCT won't help much as I don't have a fields "delete" or "Keep" in my table yet.

 

[Alansidman]

My apologies. I have reread your original posting. This is a better tutorial on eliminating duplicates.

http://www.datapigtechnologies.com/flashfiles/duplicateproblem.html

Alan

 

[Alansidman]

Crosspost: http://www.accessforums.net/access/query-iff-cell-cell-below-same-column-19436.html#post91192​

 

[qzst66]

Thanks for all the suggestions, but none of those seems to solve the issue.
My goal is to have access indicate which records should be deleted. It shall not delete them automatically.
It should only update that line with the word "Delete" if the PO number exists in the line or field below it. The table is sorted by po number and dates ascending. What I need is the PO number with the latest date and all the dates are different. There is no specific date I want to choose as a criteria (WHERE). Can somebody help?
In Excel this seems to be a very easy process.

Example:

This is what I have right now.

PO Date
451 11/11/11
451 10/13/12
452 09/04/10
452 09/09/11

Below is a table that I want access to do for me.
I want access to add another field that indicates which records to delete

PO Date Check
451 11/11/11 delete
451 10/13/12 keep
452 09/04/10 delete
452 09/09/11 keep

In Excel this seems to be a very easy process.
The formula in Excel is =IF(A1=A2,"delete","keep").

 

[gemma-the-husky]

this all comes back to the basic premise that a database although it resembles a spreadsheet, is quite different.

in terms of access, how would access know which of the two duplicates you want to delete?

the point that is probably very hard to appreciate is that access does not have an intrinsic "row" order. Unlike excel. Access may one time show records in order 1 and 2, and the next time as 2 and 1. There is no concept of "order" within an access table. Truly!

so you just cannot say "if A1=A2". It is meaningless within relational database theory. There is no absolute row 1 or 2.

and it is also why you dont get copy and paste grab handles on a cell/column/row in an access table. every cell is independent, (although the values it may take can be restricted according to certain rules determined by other data within the database.)


What access does very powerfully, is assembles sets of data, based on any characteristics you care to establish. So it can easily tell you you have 2 orders No 451, 2 orders No 452, etc, or 30 orders in November etc etc - And in doing this, it is far more powerful than excel

but it cannot manipulate those orders in different ways, just by considering that set of orders.



so instead you need to consider

why are there duplicates in the data?

should there be a procedure to prevent duplicates?

what is the data in the table anyway?

are there other columns that you would lose by delting one of the rows, or are they all identical, as well?


now - I doubt whether you will be able to do this with a query. (precisely because access has no way of distinguishing between the items within a set). So you will need to do this in code, by processing a recordset.

indeed, it may be that these duplicates indicate that the table itself is not correctly structured

hope this makes sense, although it may not be so helpful.