Losing records in query when joining Table

[BillBee]

If you open the query in the file it shows all the records I want. When you switch to the design view and join the JudgeName fields between the tblJudge and tblSections two records drop off. Could any one tell me why this is happening and what to do to show the addresses for the 21 records. Many Thanks

 

[Beetle]

It's dropping records because some of the names in your Judges table have a leading space but the related name in the Sections table do not, therefore they don't match. You shouldn't be storing the name as the Foreign Key in tblSections the first place. You should be storing the JudgeID, then you wouldn't need to worry about spelling and trim issues and your indexing and relationships would be more efficient.

 

[BillBee]

Thanks for the leading space help. Could you please explain more with respect ot the Foreign Key comment. I have added the JudgeID to the Sections Table but it seems to make no difference.

 

[jdraw]

Bill,

I think you will enjoy this article.
http://forums.aspfree.com/attachment.php?attachmentid=4712