[dba-SQLServer] Access to SQL Q

(See reply on AccessD)
J


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Darren DICK
Sent: 08 January 2006 10:34
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] Access to SQL Q


Hi all
Cross posted to AccessD
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
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com