inner join question

[silverxx12]

Hi I have two tables with names and amounts

Table 1 - contains a whole range of names
Table 2 - contains ONLY some of the names ON TABLE 1

i created an inner join between the names on both of these table as i need the amounts from tble 1 with the names of table 2



Is it possible to also get a list of names and amounts from table 1 which DO NOT APPEAR IN TABLE 2

this was code used to find the similar names on tbl 2 with amounts from tbl1

SELECT tbl2name, tbl1Amount
FROM list2 INNER JOIN list1 ON tbl2name = tbl1name;

[orange]

Research Left Join. You could also try the UNMATCHED query wizard in Access.

[silverxx12]

tried the left join and right join

SELECT tbl1.Name, tbl1.Amount
FROM tbl1
LEFT JOIN tbl2
ON tbl1.Name = tbl2.Name

the left join outputted all the names

the right join (just replaced word left join above ) returned all the names in table 2 which apeared on tabl 1

unless i did something wrong ?

Unmatched - really cool feature totally overlooked that - it did the job

But just for future reference did i do anything wrong on the joins ?

[orange]

see post #14 at this site

http://www.access-programmers.co.uk/...d.php?t=221837

[silverxx12]

yes thanx much appreciated

i had left the is null expression out in my first left join hence i was returning everything

once i used the unmatched wizard which put that code in i could see where i was going wrong

many thanks for the help leanrnt a good many tricks

[orange]

Glad to help