When you open an Access database, parts of it are loaded into memory. When the user opens the same database, parts of it are loaded into memory on their PC. You now have parts of the same physical file in memory on multiple PCs. What could possibly go wrong
We've gone through all the things that are wrong with the query you posted. Now you seem to have "fixed" the data and that broke the query for you. Makes no sense to me.
Remove the third table. Use a DLookup() to retrieve it ONCE in the load event of the report. You can then store it as a tempVar and use the TempVar in the calculation in the report.
Make sure that the data in the other two tables actually relates via the Solicitor. What happens when you run the query with just those two tables?
Did you ever remove the duplicate fields?