Hmmm... why is this join not working? query is empty.

[LauraB]

It's been awhile, and apparently I'm rusty. I'm doing what I think is a basic Join for a query, using the design view, and the query is not returning anything. Any help appreciated.

I am importing data from a web based sales tracking tool via XL to be able to do some detailed one-time reporting. I am limited on what can be exported from the sales tool so am making due with some fields as keys, unlike a db designed from scratch.

The first table imported is ClientTeacher with the fields:
Client (text field)
Teacher (text field)
I have reviewed the Client field and have confirmed there are no duplicates and am using this as a primary key.

The second table imported is Purchases with the fields:
ID (autonumber)
Client
Item
Category


Sale Date
and other fields.
On import I added the ID field as a key. Client again is a text field and since they are from the same imported source the names are in the exact same format as ClientTeacher.

I would like to get a list of purchases, but have the teacher be listed next to each purchase. If ClientTeacher is not an exhaustive list I'm fine with nulls in those records for now.
Here's the SQL I have:
SELECT Purchases.Client, ClientTeacher.Teacher, Purchases.Category, Purchases.[Sale Date]
FROM ClientTeacher RIGHT JOIN Purchases ON ClientTeacher.Client = Purchases.Client;

This query returns a list of purchases, but the Teacher field is empty. I have done some sampling and see that the text fields for most of the ClientTeacher.Client and Purchases.Client fields match.
Doing an INNER JOIN returns nothing.
What am I missing? Is my query wrong? Is there more that I need to do to my imported data? Thanks!

[JoeM]

Since you are only joining on one field, and your INNER JOIN returns nothing, it means that there are no records where your join condition is true:

ClientTeacher.Client = Purchases.Client

Check the length of your Client field in both tables. Do any have any extra spaces or special characters at the beginning or end?
I have seen this issue before with data that comes from the web, sometimes there are invisible special characters on the end, like non-breaking spaces.
You should be able to confirm this pretty easily using the LEN function on the Client field in each table. If your field value is "Bob", but returns a length of 4, you know it has some special extra characters. So you will have to clean-up the data before being able to use it.

[LauraB]

Thank you Joe, your comments helped a lot.

I was able to use the LEN function in XL to see that the fields were the same size. So I set up some other formulas to compare the fields.

I noticed with an equivlance formula in XL if I deleted the space between last and first name the two fields were equal. It turns out that one report from the web tool was exporting a Unicode 0020 (space) character between the last and first names, and for the other report the tool was exporting a Unicode 00A0 (no-break space) character. Thus making the text strings not equal because of the different space characters.

Fun with data from web! Nice to know I can still write a query. Thanks for your help.