table 1

ID name score 1
01 bill 12
02 sam 14
03 john 20
04 mike 18

table 2

ID name score 2
01 bill 32
03 john 20


i'd like to run a query that returns


ID name score 1 score 2
01 bill 12 32
02 sam 14
03 john 20 20
04 mike 18

but when i try to contruct the query i only get bill and john returned becuase sam and mike have missing values. i've messed around a bit with nz but to no avail

any help appreciated

thanks

You need to change the join to include all records from table 1 Left Inner Join and as described use NZ() to report null values.

David

You need to change the join to include all records from table 1 Left Inner Join
David:

Wouldn't that be an OUTER join? An INNER join matches record to record and an Outer join returns all records from one table and only those that match from another and showing nulls where there are no matching records for the other table.