delete records that have two fields with the same value

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" wrote in message
. net...
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

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 two
records have identical values in the same field? Is that right? If so
here is one thing you can do.

You can use an append query. On the main DB window right click on the
table you wish to delete the records from. Click 'Copy' then right
click on the main DB window's white screen and select 'Paste'. Key in
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 the
field you have duplicate values in. Set this field to the primary key
then save and close the table. Create an append query consisting of
all fields from the original table. Set the query to append the
records to the new table you have just pasted. run the query. You
will be propmted with 3 message boxes...click 'OK' on all 3 of
them...because of the primary key, only one unique values are allowed
therefore only one record with each value is appended. Let me know if
you have any problems.

Cheers.

Shiner


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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" wrote in message
u...

"L. T. Portella" wrote in message
. net...
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

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

On Thu, 13 May 2004 03:06:45 GMT, "L. T. Portella"
wrote:

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.

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.

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

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
"L. T. Portella" wrote in message
. net...
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" wrote in message
u...

"L. T. Portella" wrote in message
. net...
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

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

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


"Shiner452" wrote in message
...

I'm not quite sure what you are saying but I think you mean that two
records have identical values in the same field? Is that right? If so
here is one thing you can do.

You can use an append query. On the main DB window right click on the
table you wish to delete the records from. Click 'Copy' then right
click on the main DB window's white screen and select 'Paste'. Key in
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 the
field you have duplicate values in. Set this field to the primary key
then save and close the table. Create an append query consisting of
all fields from the original table. Set the query to append the
records to the new table you have just pasted. run the query. You
will be propmted with 3 message boxes...click 'OK' on all 3 of
them...because of the primary key, only one unique values are allowed
therefore only one record with each value is appended. Let me know if
you have any problems.

Cheers.

Shiner


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

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" wrote in message
...
On Thu, 13 May 2004 03:06:45 GMT, "L. T. Portella"
wrote:

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.

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.

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

"L. T. Portella" wrote in message
. net...
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" wrote in message
u...

"L. T. Portella" wrote in message
. net...
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

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

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" wrote in message
.. .
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" wrote in message
...
On Thu, 13 May 2004 03:06:45 GMT, "L. T. Portella"
wrote:

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.

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.

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