if record exist show field else do not show field 1

[evr72]

Hello,

I have a query that compares part numbers from one table to another.

SELECT OPERATIONSTYPE_Crosstab.ITNBR, IIf(OPERATIONSTYPE_Crosstab.ITNBR=OPERATIONS.PARTNUM,OPERATIONS.MATERIAL,OPERATIONSTYPE_Crosstab.DRILL) AS Material
FROM OPERATIONSTYPE_Crosstab LEFT JOIN OPERATIONS ON OPERATIONSTYPE_Crosstab.ITNBR = OPERATIONS.PARTNUM

This gives me
ITNBR PARTNUM Material
0035-U DRILL
0036-03 0036-03 #2308-12 PLENCO


This works well, I am not sure if this can be done but what I would like is if the parts are equal grab the fields from "OPERATIONS" else if they do not match grab the fields from "OPERATIONSTYPE_Crosstab"

If the parts are equal

ITNBR PARTNUM Material INSERT DRILL DRILLQTY
0036-03 0036-03 #2308-12 PLENCO YES YES 1

If the parts are not equal

ITNBR PARTNUM Type1 Type2 Type3
0035-U DRILL CBORE


can this be done?

 

[Andrzejek]

Will you ALWAYS get 2 records out this query?
Can you get just 1 record? Or 17 records?

Have fun.

---- Andy

 

[evr72]

Andrezejek,

it can be more than 1 record

 

[PHV]

Something like this (as a starting point) ?
SELECT T.ITNBR, Nz(O.MATERIAL,T.DRILL) AS Material, Nz(O.INSERT, T.Type1) AS [Insert]
FROM OPERATIONSTYPE_Crosstab T LEFT JOIN OPERATIONS O ON T.ITNBR = O.PARTNUM

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[evr72]

PHV,
Is not exactly what I was looking for but I think that will get me on my way.
what does the "Nz" mean I have not seen that expression before

 

[PHV]

Nz(x,y) equal y if x is null, x otherwise.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886