[AccessD] A2003: SQL to Access Q`

John
Brilliant - Absolutely brilliant
I had no idea a select statement could be buried inside the SQL like that

Yes...

SELECT I.InvoiceNo, ARP.PointsAmount AS Pending
FROM Invoice AS I LEFT JOIN [select * from AccountRewardPoints as ARP where
ARP.PointsStatusCode = 'PP' ]. AS ARP ON I.InvoiceNo = ARP.InvoiceNo
GROUP BY I.InvoiceNo, ARP.PointsAmount
ORDER BY I.InvoiceNo;

Works in Access and returns 31159 records as expected

Thank you

Darren

 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DJK(John) Robinson
Sent: Sunday, 8 January 2006 10:50 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com