[AccessD] A2003: SQL to Access Q`

Hi all
Cross posted to dba-SQL
 
I have just discovered a significant (and disturbing for me) difference in SQL
and Access that
I never thought about - I'll explain the problem hoping that someone can explain
any work around options I have
 
In SQL I use the following SQL to return 31159 Records - That's the expected
number by the way
 
SELECT I.InvoiceNo, ARP.PointsAmount as Pending
FROM Invoice as I
LEFT JOIN AccountRewardPoints  ARP
ON ARP.InvoiceNo = I.InvoiceNo
AND ARP.PointsStatusCode = 'PP'
Order By I.InvoiceNo
 
In Access if I use that SQL It errors on the Join - IE I can't create joins on
what is 
kind of a Criteria - in this case PP on the PointStatusCode field
 
I am learning the reason
 
When I paste the same SQL into the SQL query designer in enterprise manager (The
one that looks like Access's Query Designer)
- You get there by...
Right clicking ANY SQL table in Enterprise Manager
Select OPEN TABLE
Select QUERY
 
When I do this I see an unusual join line/link that goes not from a field to a
field as you expect in Access
but from the Table name to Table name with a small F and a small X next to each
other to signify the join type
 
I appreciate this means show me all and I mean ALL records (In my case) from
Invoice table IE 31159 of 'em
And it actually creates a Join on the PP Criteria - I think
 
That's all well and good - but how do I get something like that happening in
Access?
 
<big sigh>
 
Darren