If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
access 2003 join not returning expected records
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? |
#2
|
|||
|
|||
access 2003 join not returning expected records
On Jul 11, 4:27*pm, dbnoodler
wrote: 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.Field110; 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.Field110; 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.) |
#3
|
|||
|
|||
access 2003 join not returning expected records
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. " wrote: On Jul 11, 4:27 pm, dbnoodler wrote: 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.Field110; 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.Field110; 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.) |
#4
|
|||
|
|||
access 2003 join not returning expected records
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 "dbnoodler" wrote in message ... 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? |
#5
|
|||
|
|||
access 2003 join not returning expected records
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 |
#6
|
|||
|
|||
access 2003 join not returning expected records
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. "Michel Walsh" wrote: 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 "dbnoodler" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|