Allow to enter criteria at the join level
Left join categories c on c.id = p.catgoryId and p.price > 100
This can be done in most SQL DBs, including SQL Server, but is not allowed in Access
Dick Watson commented
You can do this by editing the SQL by hand in the pathetic Assess SQL editor. Once you do this, the QBE grid becomes useless and/or destructive to your query.
As a more generalized solution, this should be added to the QBE grid as, say, a click box on the relation line that allows a non equi-join to be defined/indicated for that relationship. QBE should also get the ability to define/edit a subquery.
Andy Rice commented
I would use this.