SQL -How to code to have an indicator in the right place?

[iren]

Hi!
I have three fields in the file: ID; PRODUCT, TYPE.

H; P -are appropriate products
F; M -are inappropriate products

A; I -appropriate TYPE
N; T -inappropriate Type

Let’s say there are several records with the same ID and different products and Types. As long as a member has correct Product (H; P) and only one record meets Type criteria (A; I) I do NOT mark down none of the records with the same ID

For example –Not a subject for mark down:

ID Product Type Mark down
*******************************
M1 F N
M1 F T
M1 H A

Not a subject for mark down either:

ID Product Type Mark down
*******************************
M1 F N
M1 F T
M1 H T

However if one record is “good” and the rest of the records (with inappropriate Product F; M) have appropriate TYPE (A; I) I need to mark down ONLY record with appropriate PRODUCT & TYPE

For example – this is a subject for mark down with “S”:

ID Product Type Mark down
*******************************
M1 F A
M1 F I
M1 H A S

Could you help me with this query?

 

[nicsin]

try this

UPDATE 
myTable 
SET 
mark = 's'
WHERE 
id in (
SELECT 
DISTINCT id 
FROM 
myTable 
WHERE 
id in (
SELECT 
DISTINCT id 
FROM 
myTable 
WHERE 
product in ('F','M') 
and type in ('A','I');  
) and id  in ( 
SELECT 
DISTINCT id 
FROM 
table2 
WHERE 
product in ('H','P') and type in ('A','I')  
) 
) and product in ('H','P') and type in ('A','I');

N

 

[iren]

Nicsin, thank you for your response!

However I wonder what is Table2? I mean...I have only table...
Does it means that table2 is a copy of original MyTable?

Thank you!

Iren

 

[nicsin]

er...no just a typo. replace mytable and table2 with the name of your table

 

[iren]

Oops!
It said Error: You cannot reopen mytable for update access with member-level control because mytable is in use


Since I am sure it is not in use by anybody else (including myself) then...it is something about code

 

[nicsin]

it actually ran on a test database that I've got. Are you sure you don't have the table open within access when you run the query?

N