Query problem.

[Stone]

First of all, sorry for my poor english. I have a query problem. I
want to delete som files from my table, but there is a condition and I
can not do the right thing. There are 3 fields (there are more, but
don't mind) who have the information needed. I put an exemple:


I have this files:
File 1
Field1 = 1
Field2= x
Field3= 500

File 2
Field1 = 25
Field2= o
Field3= 1

File 3
Field1 = 2
Field2= x
Field3= 501

File 4
Field1 = 3
Field2= o
Field3= 502

File 5
Field1 = 35
Field2= o
Field3= 2

I need make a query who deletes the 4th file, because its field1
doesn't have any field3 (in any file) with the same value, and its
field is = o
File 1 have a field1 = file 2 field3, so they must keep (file 1 and
file 3)
File 3 have a field1= file 5 field3, so they must keep too (file 3 and
file 5)

I hope somebody can help me. Thanks in advance.

 

[MacDermott]

I think what you are calling "files" are usually called "records" in
English.
If your table is named MyTable, you could use SQL like this:
(watch for word wrap!)

DELETE * FROM MyTable WHERE Field2="o" AND Field1 IN SELECT Field3 FROM
MyTable

HTH

 

[Stone]

Thanks, MacDermott. Yes, they are records. I try your SQL instruction,
but it doesn't works. I have an error message: IN operator without ( )
Do you know where is the problem?
Thanks in advance...

 

[MacDermott]

Try it like this:

DELETE * FROM MyTable WHERE Field2="o" AND Field1 IN (SELECT Field3 FROM
MyTable)

 

[Stone]

Thanks again, Mcdermott. I'd tried it, but it doesn't works. I made a
query with this SQL code but it doesn't works. There are no files to
delete.

 

[MacDermott]

Could you post the exact syntax of the query you're using?

 

[Stone]

Of course. The syntax is the same as you write before, I only change
the name of the fields and the table. The exact is (I use cut and
paste):
DELETE *
FROM MiTabla
WHERE Campo2="o" AND Campo1 IN (SELECT Campo3 FROM
MiTabla);

Thanks again, Mcdermott, and sorry for the time you spend...

 

[MacDermott]

Hmmm...

Is it possible that your Field2 contains a numeric 0, rather than a literal
"o"?

If that's not the case, perhaps you could post the data from just a few
records of your table, including at least one record which should be
deleted...

 

[Stone]

No, all the fields have a text value, not a numeric value. While I'm
testing the query I use the values that comes in the exemple here. The
same values, but all are text values.
I can send you the database that I use in test, but they have only the
records I put here.
Thanks again for you interest, Mcdermott...

 

[MacDermott]

OK, I looked again.
Try this syntax:

DELETE *
FROM MiTabla
WHERE Campo2="o" AND Campo1 NOT IN (SELECT Campo3 FROM
MiTabla);

Please note, however, that your Record 2 should also be deleted, as there is
no record with Campo3="25".

 

[Stone]

Thanks, again, Mcdermott, you have a lot of patience with me, but it
doesn't works. I try it and deletes 3 records: Record 2, 4 and 5. I
think it's too dificult to do what I want. Maybe I don't explain it
correctly.
I have an excel table and I import it with access. There are some
records that are duplicated. There are records who have Campo1 with
the same value than other record in Campo3, those are the ones that I
must keep, and delete the others, but not all, only the ones who have
Campo2="o" and they have not Campo3 with the same value than other
record in Campo1. I know it's dificult, so many thanks for your
answers...