[AccessD] A2003: SQL to Access Q`

Darren

I haven't got SQLS instantly handy (in process of reconfiguration), so I
can't easily check what results you're expecting from it.  Nor am I sure
about your table design.

However, I do understand Access's doubt about ambiguity - I think - as to
when/how to apply the criterion which is not strictly part of the join.  In
Access, does this give the answer you are looking for?

SELECT I.InvoiceNo, ARP.PointsAmount as Pending
FROM Invoice as I
LEFT JOIN 
(select * from AccountRewardPoints where ARP.PointsStatusCode = "PP" ) as
ARP
ON ARP.InvoiceNo = I.InvoiceNo
Order By I.InvoiceNo

John


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK
Sent: 08 January 2006 10:33
To: 'Access Developers discussion and problem solving'
Subject: [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
 
 
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com