Confusion around joins

[choaspes]

Hi All

I wonder if anyone can help me with a problem around joins in a query?

Let's say I have tblOrders and tblPeople. tblOrders contains a PersonID field corresponding to a record in tblPeople. Each record in tblPeople has a PersonName text field.

The tables are set as related in "relationships", between the ID field of tblPeople and the PersonID field of tblOrders - enforce referential integrity and both the cascade options.

I want to return records from tblOrders that meet certain criteria, but in the query results include the PersonName field from tblPeople.

Left join seems to return all the records I want from tblOrders (according to whatever criteria I set) but gives null values for PersonName for all records in which PersonID is a different value to the most recent record in tblOrders matching the criteria.

How can I get the query to display PersonName values for all of the results?

Thanks in advance

 

[plog]

You can't create data for which there is none. A LEFT JOIN returns all data from one table, no matter if there's data in the other table. So, for the records you are getting a blank PersonName value, that means there are no corresponding records in tblPeople for the PersonID in tblOrders.

You can verify this by picking out one record in your query with a null FirstName , seeing what the PersonID is for it (if its there at all) and then manualy looking up that PersonID in tblPeople--it won't exist.

 

[CJ_London]

The implication of what you are describing is that you have values in tblOrders.PersonID which does not have a matching value in tblPeople.PersonID.

It is also possible you have made the mistake of using lookups in your table definitions which often causes this type of 'misunderstanding' - If you are using them, remove them and all should suddenly become much clearer.

 

[choaspes]

Thanks for the responses chaps. I would dearly love the solution to be as simple as described, but I can categorically assure you that every value of PersonID in every record in tblOrders corresponds to a valid ID number for a record in tblPeople and that every record in tblPeople has a populated PersonName field. The data does exist.

I will investigate "lookups". I have never knowingly set any, but I suppose I may have done. I deducde it can only be this that's wrong! Thanks again.

 

[CJ_London]

If you are still stuck I suggest you post a small db with the two tables and the query so we can take a closer look