need query

[syodb]

Access database 2003:

Table1 (T1) has the following fields:

1       n1	n6	n7	n8
2	n2	n6	n7	n8
3	n3	n6	n7	n8

Table2 (T2) has the following fields:

1	n1	n10	
2	n2	n11	
3	n1	n12

Need query to return the following:

n1	n6	n7	n8	n12
n2	n6	n7	n8	n11
n3	n6	n7	n8	"NA"

Tried everything in many ways using the query design aid, none worked.

 

[Galaxiom]

Join the two tables on the second column. Double Click (or right click and choose Join Type) the join and select the appropriate type of join, "all records from T1 etc"

This is caller an OUTER JOIN

 

[syodb]

Tried left or right outer join and still did not do the trick. How about an actual query statement that uses outer join or not and does the trick?.

 

[arnelgp]

Select T1.field2,T1.field3,t1.field4,field5,iif(dmax("field3","T2","t2.field2='" & t1.field2)=0,"NA",dmax("field3","T2","t2.field2='" & t1.field2)) as expr1 from t1

 

[syodb]

SELECT Q1.f1, Q1.f2, Q1.f3, Q1.f4, Q1.f5
FROM (SELECT T1.field2 AS f1, T1.field3 AS f2, T1.field4 AS f3, T1.field5 AS f4, T2.field3 AS f5, T2.ID AS ID
FROM T1 LEFT JOIN T2 ON T1.field2=T2.field2) AS Q1 INNER JOIN (SELECT T1.field2 AS f1, T1.field3 AS f2, T1.field4 AS f3, T1.field5 AS f4, Max(T2.ID) AS ID
FROM T1 LEFT JOIN T2 ON T1.field2 = T2.field2
GROUP BY T1.field2, T1.field3, T1.field4, T1.field5) AS Q2 ON (Q1.f1=Q2.f1) AND (Q1.f2=Q2.f2) AND (Q1.f3=Q2.f3) AND (Q1.f4=Q2.f4) AND ((Q1.ID=Q2.ID) Or (isnull(Q1.ID)));

tried the above and seem to work, any better way to do this? see the isnull(Q1.ID).

Got the #ERROR# for expr1 when using the below in the query acces 2003:
Select T1.field2,T1.field3,t1.field4,field5,iif(dmax("fie ld3","T2","t2.field2='" & t1.field2)=0,"NA",dmax("field3","T2","t2.field2='" & t1.field2)) as expr1 from t1

 

[arnelgp]

Select T1.field2,T1.field3,t1.field4,field5,iif(dmax("fie ld3","T2","t2.field2='" & t1.field2 & "'")=0,"NA",dmax("field3","T2","t2.field2='" & t1.field2 & "'")) as expr1 from t1

 

[syodb]

arnelgp, your last offered solution works perfect if =0 check is replaced with insull check.

Galaxiom's offered suggestion works perfect in general.

Thanks you arnelgp and Galaxiom.