Query not returning all of the results

[velvettiger]

Hi Guys,



I was given a list which contain a list of student's firstname, lastname, street line 1, street line 2, street line 3 and country.This list has in 400 unique students names.The name of the list is called GR_list.

I need to add the student's telephone numbers. These numbers are found in a table called student_summary. The student_summary list contains Id, firstname, lastname, street line 1, street line 2, street line 3,telephone numbers etc. This table has in thousands of rows.



The first thing i did was import the list in access and then the following code was written

select  distinct s.ID,g.firstname, g.lastname, g.streetline1, g.streetline2, g.streetline3 g.country,s.telephone

from GR_list g inner join student_summary s  on  (g.firstname= s.firstname) and (g.lastname=s.lastname) and (g.streetline1=s.streetline1) and (g.streetline2=s.streetline2) and (g.streetline3=s.streetline3) and (g.country=s.country)

When I checked the results only 40 out of the 400 person were returned. And all 400 person are in the list. Could any one tell me why this is?

Thanks

[pbaldy]

First off I noticed this:

...g.streetline=s.streetline3...

With joins on all those fields, the data in each field in the two tables would have to match exactly. Does it?

[velvettiger]

First off I noticed this:

...g.streetline=s.streetline3...

With joins on all those fields, the data in each field in the two tables would have to match exactly. Does it?

yes it does match exactly but also that line is suppose to be g.streetline3=s.streetline3.I know for sure that this query should returning more than 40.

[pbaldy]

You're saying the query has the 3 in the first one? If so, I would examine a record that you think should be included and check each field in each table. From Access's perspective, these don't match:

123 Main St
123 Main Street

I suspect you'll find that something in one of the fields is different than the corresponding field in the other table. If not, can you post the db?

[velvettiger]

Hi all,

I checked the file and all of the addresses that I checked so far matches the list provided to me. Does the code look right though?