Help with Join Relationships in Design View

[robintmathew]

I'm trying to join two tables. One table contains multiple addresses. Second table contains addresses I do not want from the first table. When I run it I want it to populate with all addresses excluding those from the second table.

I read somewhere that this is done in SQL view and not design view. It looks like this:
SELECT
FROM [32 Names] INNER JOIN [Final Address] ON [32 Names].FULL_NAME = [Final Address].FULL_NAME;



Final Address contains all of the addresses. 32 Names contains those addresses I do not want.

How can I do this?

[weekend00]

SELECT
FROM [32 Names] Left JOIN [Final Address] ON [32 Names].FULL_NAME = [Final Address].FULL_NAME where [final address].full_name is null

the where clause excludes the address in table 2.

And for sure you can design it in design view, and it is much easier than do in SQL view.

[robintmathew]

If its not too much to ask could you explain how I can do it in the design view. I'd like to learn that for future reference.

[robintmathew]

Actually it looks like this. Basically same situation. I just wanted all the information to come down from the Final Address such as the name, fax number, address, etc. EXCLUDING the names from the 32 Names File. So in an instance like this how can it be modified?


SELECT [Final Address].*, [Final Address].FULL_NAME, [Final Address].ID, [Final Address].Field3, [Final Address].FAX_NUMBER, [Final Address].COUNT1, [Final Address].ADDRESS, [Final Address].CITY, [Final Address].ST, [Final Address].ZIP, [Final Address].PHONE
FROM [32 Names] INNER JOIN [Final Address] ON [32 Names].FULL_NAME = [Final Address].FULL_NAME;

[weekend00]

SELECT [Final Address].*, [Final Address].FULL_NAME, [Final Address].ID, [Final Address].Field3, [Final Address].FAX_NUMBER, [Final Address].COUNT1, [Final Address].ADDRESS, [Final Address].CITY, [Final Address].ST, [Final Address].ZIP, [Final Address].PHONE
FROM [32 Names] Left JOIN[Final Address] ON [32 Names].FULL_NAME = [Final Address].FULL_NAME where [final address].full_name is null

I don't think I can explain well how to do it in design view, because it's hard to understand to say "click on something", "right click on something", then "type something to somewhere";

design view is easy to use but hard to explain;
SQL view is hard to type but easy to follow (just copy and paste).

if you have strong imagination, you may follow these words:
1 in query design view, add the two tables
2 drag [full_name] from table [32 Names] and drop to [full_name] in table [final address], a link will be setup between the two fields
3 double click on the link line, "Join Properties" window will pop up. (if you double click not on the link line, "query Properties" window will pop up, please try double click on the link line again until the "Join Properties" window show)
4 there are 3 options, select the second option "2: Include All....." then click OK button
5 add all fields that you want from [32 Names] to selected field list
6 add field [full_name] from [final address] to selected field list, but unshow this field, type "null" in criteria of this field.
7 run query.

[robintmathew]

Hmmm. I'm getting zero results. Not sure why. I've followed it to the tee and there are names in there that I can see exist which do not belong. So it is there.

32 Names file contains all the addresses I do not want.
Final Address contains all of the addresses.

Results should yield All addresses excluding the ones on the 32 Names file. Hmm

[weekend00]

use right join then:

SELECT [Final Address].*, [Final Address].FULL_NAME, [Final Address].ID, [Final Address].Field3, [Final Address].FAX_NUMBER, [Final Address].COUNT1, [Final Address].ADDRESS, [Final Address].CITY, [Final Address].ST, [Final Address].ZIP, [Final Address].PHONE
FROM [32 Names] right JOIN[Final Address] ON [32 Names].FULL_NAME = [Final Address].FULL_NAME where [32 names].full_name is null

[robintmathew]

Amazing. It worked. I guess it depends on how I position the two tables. In any case it worked. Thanks alot! Glad to know there is site like this full of savvy Access users to help the not so savvy. Thanks again

[weekend00]

you are always welcome.