access 2007 add column using sub query

Question

I am trying to add two columns from a new table into my current query results. Something like this:

In SQL I would do something like:

Select A.Clm1
,A.Clm2
,B.Clm3
,(select udf_number from newTable NT where NT.udf_type_id=1 and NT.id=A.id) as NewColumn1
,(select udf_number from newTable NT where NT.udf_type_id=2 and NT.id=A.id) as NewColumn2
from TableA A inner join TableB B on A.id=B.id inner join newTable NT on NT.id=A.id

or even using case something like

Select A.Clm1
,A.Clm2
,B.Clm3
,(case when NT.udf_type_id=1 then NT.udf_number) as NewColumn1
,(case when NT.udf_type_id=2 then NT.udf_number) as NewColumn2
From..... ...

I tried a few things in access, using sub queries in the from or where part. but didn't get any success. My prb is that I am trying to add two columns based on 1 column in the new table. any help in getting this done in Access?

Answer 1

This is valid for access97 and above (I guess)

Select A.Clm1, A.Clm2, B.Clm3,
NT1.udf_number as NewColumn1,
NT2.udf_number as NewColumn2
from 
TableA A 
inner join TableB B     on A.id=B.id 
left  join newTable NT1 on NT1.id=A.id and NT1.udf_type_id = A.id - A.id + 1
left  join newTable NT2 on NT2.id=A.id and NT2.udf_type_id = A.id - A.id + 2

Please observe the tricky part A.id - A.id + 1 needed by Access
For other RDBMs (or perhaps newer access versions) it can be:

Select A.Clm1, A.Clm2, B.Clm3,
NT1.udf_number as NewColumn1,
NT2.udf_number as NewColumn2
from 
TableA A 
inner join TableB B     on A.id=B.id 
left  join newTable NT1 on NT1.id=A.id and NT1.udf_type_id = 1
left  join newTable NT2 on NT2.id=A.id and NT2.udf_type_id = 2

Answer 2

Join newTable twice.

In SQL, it would look something like this:

SELECT A.Clm1, ...
       NT1.udf_number AS NewColumn1, NT2.udf_number AS NewColumn2
  FROM A 
       INNER JOIN newTable NT1 ON A.id = NT1.id
       INNER JOIN newTable NT2 ON A.id = NT2.id
 WHERE NT1.udf_type_id = 1
   AND NT2.udf_type_id = 2
   AND ...

So, basically, you pretend to have two different newTables: One has only the type 1 entries, the other one only the type 2 entries. And you join both to A.

In the Access query designer, just add the table twice. Access will probably call them newTable and newTable1, you might want to change that to something more descriptive. Then add NT1.udf_type_id and NT2.udf_type_id to the list of columns and add the filter values (1 and 2, respectively).

(Note: This will only return records from A for which both a type 1 and a type 2 record in newTable exist. If you want to return all of A instead, use a LEFT JOIN instead of an INNER JOIN and add the type criterion to the join condition.)