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

From: Stephen Glynn (stephen.glynn_at_ntlworld.com)
Date: 05/13/04 

Date: Thu, 13 May 2004 13:43:26 +0100

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

Relevant Pages

  • Re: Group By, Max and Min
    ... SELECT field1, field2, field3, LAST, MIN, MAX ... I have (from a previous saved query) these fields: ...
    (microsoft.public.access.queries)
  • Re: Using flexible query criteria
    ... criteria for the query is based on the user's input in the main form. ... One of the user inputs is a field called "Vendor_Name" (in the form of ... and said that Field1 is the primary key and is an autonumber field, and Field2 is the field shown in the combo box, you must modify it to a UNION select, similar to ...
    (comp.databases.ms-access)
  • Re: Count Problem
    ... Access MVP 2002-2005, 2007-2008 ... Query as below: ... But after running, the result of field2 is correct, while field1 is the total records number of table x, not what I wanted. ...
    (microsoft.public.access.queries)
  • Re: Query problem - How do I do this?
    ... You may be able to normalize your table with a union query ... SELECT 1 as Fld, Field1 as TheValue, Countas NumOf ... FROM tblTooManyFields ... SELECT 2, Field2, Count ...
    (microsoft.public.access.queries)
  • Re: Combo-Box list unsorting
    ... RecordSource property and select it. ... it'll take you to the query. ... drag-dropping fields from Table1 to the Form. ... want one of the fields (say Field2) in Table1 and the Form. ...
    (microsoft.public.access.gettingstarted)