Query involving Two Left Joins to the same table

[John_sc]

Ajax, Thanks. I had not thought of a crosstab, but you are correct. That would give me what I was looking for. It also elegantly handles the case where I have 10 alternate Addresses for one ID.

[Micron]

I am picking option 2 which states "Include All records from 'Table1' and only those records from 'Table2' where the joined fields are equal." It is not returning "All records from Table1".

I can get confused about what to expect by those join statements sometimes. I think you will get what you expect if you remove the Nbr field from the first table, which was blank in your sample anyway.
As for the Unions, based on your first posts, I thought you'd only need ten.