Query not connecting correctly

[KateM]

The problem I have is that this database was already started and I am trying
to get it to work and going crazy. The person who started it had lookups for
Contacts and Organization. I removed those due to seeing comments about
lookup fields. Now I have different issues going on.
I have this query:
SELECT [CGR Projects].[Project#], [CGR Projects].ProjectDesc, [CGR
Projects].Organization, [CGR Contacts].Contact, [CGR Contacts].Title, [CGR
Contacts].Fname, [CGR Contacts].Lname, [CGR Contacts].Position, [CGR
Contacts].Phone, [CGR Contacts].Address1, [CGR Contacts].Address2, [CGR
Contacts].City, [CGR Contacts].State, [CGR Contacts].Zip, [CGR Contacts].Email
FROM [CGR Contacts] RIGHT JOIN [CGR Projects] ON [CGR Contacts].ClientID =
[CGR Projects].ClientID;

Only the Project# and ProjectDesc are displaying even though there are
contacts that should be there.
We are a non-profit research company that has Reports dated back to 1915.
They want to be able to have contact information related to the project and
report. I keep running into problems.
There are 5 Tables.
CGR Projects
ProjectContracts
CGR Clients (which holds all the organization names)
CGR Publications (which has all reports from 1915-now with Titles)
CGR Contacts (there is a project contact and a regular contact.)
I would be greatful for any help or input.

 

[Jim C.]

Hi Kate,

In revieiwing the query the only thing I can see how this would happen is if
the clientID is not exactly the same between the 2 tables, and so you are
getting the rows from CGR Projects even though there is no match in CGR
Contacts due to the right join. If you would have used an INNER JOIN I
expect you will get no records returned.

Assuming that the field is defined as text, check for extra spaces before or
after the actual ID. You can use the trim function in your Join resolve
this if it is indeed the issue -

RIGHT JOIN [CGR Projects] ON Trim([CGR Contacts].ClientID) = Trim([CGR
Projects].ClientID)

One other possiblity would be if only numerics are used (while defined as a
text field), that one table uses lead zeroes, and the other doesn't.

Jim