access 2003 join not returning expected records

[dbnoodler]

I have a table of records of lead contact info and another that has territory
numbers assigned to zip codes. I have joined the tables at their zip code
fields to get the territory numbers for each lead and I'm not getting nearly
the number of records I'm looking for. I've checked both tables and the
records are there. The fields are same data type, size, etc. What am I
missing?

 

[pietlinden]

I have a table of records of lead contact info and another that has territory
numbers assigned to zip codes. I have joined the tables at their zip code
fields to get the territory numbers for each lead and I'm not getting nearly
the number of records I'm looking for. I've checked both tables and the
records are there. The fields are same data type, size, etc. What am I
missing?

An understanding of what a "lossy join" is? This is one way in which
old Oracle SQL was (to me) clearer than Access SQL.
Inner joins in SQL (the default join type), look like this

SELECT (Fields...)
FROM Table1, Table2
WHERE Table1.Field1 = Table2.Field2
AND Table1.Field1>10;

the first filter in the where clause is a join, so any records where
Table1.Field1 is NOT equal to Table2.Field2 get eliminated from the
result. The same thing happens in Access, except it expresses the
same thing like this:

SELECT (Fields...)
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field2
WHERE Table1.Field1>10;

Solution...
Use an outer join instead of an inner join. Change INNER to LEFT or
RIGHT (depending on which table you want to see all the values from.)

 

[dbnoodler]

I need corresponding info from both tables, so I need records from both
tables as the joined field matches so that I can update the lead contact
table from the zip code/territory table. I have found records in both tables
that match but do not show up in the query. Something appears to be filtering
them out.

 

[Michel Walsh]

Your fields values may look the same but be different due to extra space, as
example, or, if using Canadian/British code, can be with/without the 'middle
space'. A join is not 'intelligent' as if we ask =, then only those values
that are really literally equal (case insensitive) will be considered a
match.


Vanderghast, Access MVP

 

[Michel Walsh]

There is also the standard confusion between a capital o, O, and a zero,
0; and a small L, l, and a one , 1. They may look the same, given some font,
but are different, for a join.


Vanderghast, Access MVP

 

[dbnoodler]

You're right when I got both fields the same length, the query popped right
in. The Canadian postal codes were affected, but I was only looking for
domestic records anyway. I'm good to go now.