need help with Access query

Question

hi

i have 2 tables:

A: ID,Namae

B: ID,Name

i need that where A.ID = B.ID will be added colum ZZ with the value '1' else '0'

(only for show the table - i dont want to update table B)

for example:

A

1 | aa

2 | bb

3 | cc

b

1 | gg | 1

4 | hh | 0

3 | ss | 1

how to write this in Access query ?

thak's in advance

Answer 1

Normally you would create queries in MS Access via the (graphical) query-editor, so it is rather difficult to explain without the pictures.

Nevertheless, when you open the query-editor, instead of using the graphical editor you can enter the SQL-Query

SELECT B.ID, B.Name, IIf(IsNull([A].[ID]),0,1) AS ZZ
FROM B LEFT JOIN A ON B.ID = A.ID;

(join the two tables using all elements of table B but join only elements from table A when the IDs match -- FROM-clause; when there is no corresponding value in A (i.e. A is null) then return 0 otherwise 1 and call this calculated column ZZ ('AS') )

Going back to the query design view in the query designer you can see the formula as "ZZ: IIf(IsNull([A].[ID]),0,1)" on the Fields line (you can easily change the formula there as you need to). Furthermore the join in the graphical view makes it clearer how the two tables are joined (see small arrow in the line joining table A and B). When you right click on this line you can view the join properties and see how to select to join all from table B but only elements from table A, where the linked values correspond.

You would have to make sure that this does not impact your performance, but for a quick and dirty-solution this should do the trick.

HTH Andreas