CODEWITHSUNDEEP
sqlms-access
val
val

Reputation: 1709

How to query these values from two tables depending on criteria, using MS Access Design View

I am new to Access queries and SQL syntax and trying to build the following:

Given two tables A and B with fields x(key), y, z common to both tables and B.x being a subset of A.x, how would I generate a query that populates query.x from A.x, query.y from B.y, and query.z from B.z unless A.x values are unique to A, in which case query.y and query.z should come from A.y, and A.z, respectively.

Using Design View criteria I tried this (converted to SQL):

SELECT A.x
FROM A LEFT JOIN B ON A.x = B.x
WHERE B.y = IIf( A.x Like 'T*', A.y, B.y ) 
      AND B.z = IIf( A.x Like 'T*', A.z, B.z )

I used Like 'T*' because the unique x values to A begin with T and I thought this might capture cases where there is a difference.

Upvotes: 0

Views: 42

Answers (1)

onedaywhen
onedaywhen

Reputation: 57093

Not sure whether you can reconstruct the below query using the designer:

SELECT A.x, B.y, B.z
  FROM A INNER JOIN B ON A.x = B.x
UNION
SELECT A.x, A.y, A.z
  FROM A WHERE x NOT IN ( SELECT x FROM B );

Upvotes: 1

Related Questions