delete records that have two fields with the same value

  • Thread starter Thread starter L. T. Portella
  • Start date Start date
L

L. T. Portella

I receive daily an Access 2000 table and some of the records need to be
deleted because the record may have two fields with identical value. How can
I do this. Right now I am sorting the records on these fields and then
deleting when I see the identical value on these two fields.This is very
laborious. Can someone help me ? thanks
 
L. T. Portella said:
I receive daily an Access 2000 table and some of the records need to be
deleted because the record may have two fields with identical value. How can
I do this. Right now I am sorting the records on these fields and then
deleting when I see the identical value on these two fields.This is very
laborious. Can someone help me ? thanks
Click to expand...
If you click on Queries. Then get the window for existing queries. Click on
the New button. It brings up a dialog - select find duplicates. Tell the
wizard which field contains the duplicates and it will bring both records
that have them up. Then you need to delete every second record manually.
Maybe someone else has code to handle this already.

This should save some time tho
Marc
 
I'm not quite sure what you are saying but I think you mean that tw
records have identical values in the same field? Is that right? If s
here is one thing you can do.

You can use an append query. On the main DB window right click on th
table you wish to delete the records from. Click 'Copy' then righ
click on the main DB window's white screen and select 'Paste'. Key i
a new table name (it can be temporary) then select 'Structure Only'.
Open this new table you have just pasted in design view and select th
field you have duplicate values in. Set this field to the primary ke
then save and close the table. Create an append query consisting o
all fields from the original table. Set the query to append th
records to the new table you have just pasted. run the query. Yo
will be propmted with 3 message boxes...click 'OK' on all 3 o
them...because of the primary key, only one unique values are allowe
therefore only one record with each value is appended. Let me know i
you have any problems.

Cheers.

Shine
 
Marc it is not that I have two similar records. It may be only one record
that happens to have two identical values in two different fields.
 
Marc it is not that I have two similar records. It may be only one record
that happens to have two identical values in two different fields.
Click to expand...

You can create a Delete query with a criterion. Suppose you want to
delete any record where the values in ThisField and ThatField are
equal to one another: create a Query based on your table; select the
Primary Key field and ThisField; put a criterion on ThisField of

=[ThatField]

Change it to a Delete query and make sure that the Primary Key field
has "From" on the action line.

Run the query by clicking the ! icon (or just doubleclicking the query
name in the database window) and it will delete those records.
 
In the duplicates query you can specify which field the duplicate is. It
does not test the whole record for duplicates, unless you select all the
fields of course.
Press the new query button.
Select the table that these records are in
Find the field you want in the left side of the field listing
Click on the button with > on it, not the double arrow.
Click next
Select any other fields which will tell you which record to delete
Click Next

HTH
Marc
 
Shiner
No, they are not two records. It is only one record except that that
particular record may have two different fields with the same value
 
Below are the steps that I believe you are referring to and my comments

1. Yes I want to delete any record where the values in ThisField and
ThatField are equal to one another.
2. Create a Query based of my table. This I can do without any problem
3. Select the Primary Key and This Field. How do I select these two fields
in a query?
4. Put criterion on thisfield of =[thatfield] This I can do without any
problem
5. change it to a delete query. How do I change it to delete query?
6.How do I make sure that the primary key field has "from" on the action
line
7. Run the query by clicking the ! icon. This I can do without any problem

Thank you
John Vinson said:
Marc it is not that I have two similar records. It may be only one record
that happens to have two identical values in two different fields.
Click to expand...

You can create a Delete query with a criterion. Suppose you want to
delete any record where the values in ThisField and ThatField are
equal to one another: create a Query based on your table; select the
Primary Key field and ThisField; put a criterion on ThisField of

=[ThatField]

Change it to a Delete query and make sure that the Primary Key field
has "From" on the action line.

Run the query by clicking the ! icon (or just doubleclicking the query
name in the database window) and it will delete those records.
Click to expand...
 
L. T. Portella said:
Marc it is not that I have two similar records. It may be only one record
that happens to have two identical values in two different fields.
Click to expand...
 
answers to questions:

3. in query design view, drag the two fields into the QBE grid (rows/columns
in lower half of window).

5. still in design view, on the menu bar click Query, Delete. *note of
warning: a Delete query is an action query. if you click on Datasheet view
from Design view, you see the usual datasheet. when you click on Run (!), or
if you try to open the query to datasheet view directly from the database
window, you don't get Datasheet view - instead the query executes the
action.*

6. once you change the query to a Delete query, the third *row* in the grid
is labeled Delete:, at the left side of the window. the default value in
each column is Where. in the primary key column, change Where to From.

hth


L. T. Portella said:
Below are the steps that I believe you are referring to and my comments

1. Yes I want to delete any record where the values in ThisField and
ThatField are equal to one another.
2. Create a Query based of my table. This I can do without any problem
3. Select the Primary Key and This Field. How do I select these two fields
in a query?
4. Put criterion on thisfield of =[thatfield] This I can do without any
problem
5. change it to a delete query. How do I change it to delete query?
6.How do I make sure that the primary key field has "from" on the action
line
7. Run the query by clicking the ! icon. This I can do without any problem

Thank you
John Vinson said:
Marc it is not that I have two similar records. It may be only one record
that happens to have two identical values in two different fields.
Click to expand...

You can create a Delete query with a criterion. Suppose you want to
delete any record where the values in ThisField and ThatField are
equal to one another: create a Query based on your table; select the
Primary Key field and ThisField; put a criterion on ThisField of

=[ThatField]

Change it to a Delete query and make sure that the Primary Key field
has "From" on the action line.

Run the query by clicking the ! icon (or just doubleclicking the query
name in the database window) and it will delete those records.
Click to expand...
Click to expand...
 
Back
Top