need to delete record having two different fields with same identical value

need to delete record having two different fields with same identical value

daily I am given a table (access 2000) where a record may have the same
identical value in two of its fields. I have to delete such a record. Can
anyone help me with this

L. T. Portella wrote:
need to delete record having two different fields with same identical value

daily I am given a table (access 2000) where a record may have the same
identical value in two of its fields. I have to delete such a record. Can
anyone help me with this



Assuming you're always comparing the same two fields (Field1 and Field2)
and that RecordID is the primary key, just write a query to show
RecordID, Field1 and Field2, and put =[Field1] in the criteria box under
Field2. Run the query to check it, and then turn it into a delete query.

Sounds to me as if there's something wrong with the database design,
though -- you shouldn't need to be deleting the rows if the database is
properly normalised.

Alternatively, which might be a lot safer (I hate deleting data unless I
absolutely have to), use [Field1] as the criteria for Field2 and then
base all your forms, reports and other queries on this query rather than
on the underlying table. That way the records with duplicate fields
stay there intact should you ever need them, but you don't get to see
them unless you want to.

Steve

On Thu, 13 May 2004 04:16:40 GMT, "L. T. Portella"
wrote:

need to delete record having two different fields with same identical value

daily I am given a table (access 2000) where a record may have the same
identical value in two of its fields. I have to delete such a record. Can
anyone help me with this


L.T., You have asked this question MULTIPLE times in MULTIPLE
newsgroups. You've been answered at least three times.

Create a Delete Query with a criterion on Field1 of

=[Field2]

and run it.

I've copied this to EMail - perhaps you're making the incorrect
assumption that replies will come by EMail; in fact it's typical to
come back to the newsgroup for your answers. Also, it is impolite to
"multipost" - if you wish to post to more than one newsgroup (hint:
it's usually not necessary, we who volunteer here usually cover
several groups) please crosspost instead: put two or three newsgroups
in the Newsgroups line of your newsreader.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

I use the neat trick of and this solved my most pressing problem for
which I truly thank you.
No, there is nothing wrong with the database design; it is just the way
things were set up originally and which did not contemplate the present
snag. Once again many thanks
"Stephen Glynn" wrote in message
...


Assuming you're always comparing the same two fields (Field1 and Field2)
and that RecordID is the primary key, just write a query to show
RecordID, Field1 and Field2, and put =[Field1] in the criteria box under
Field2. Run the query to check it, and then turn it into a delete query.

Sounds to me as if there's something wrong with the database design,
though -- you shouldn't need to be deleting the rows if the database is
properly normalised.

Alternatively, which might be a lot safer (I hate deleting data unless I
absolutely have to), use [Field1] as the criteria for Field2 and then
base all your forms, reports and other queries on this query rather than
on the underlying table. That way the records with duplicate fields
stay there intact should you ever need them, but you don't get to see
them unless you want to.

Steve