DJK(John) Robinson
djkr at msn.com
Sun Jan 8 17:56:49 CST 2006
My pleasure, Darren. I'm usually too far behind in reading this list to be of any use at all. I notice you've now added in a GROUP BY, and included the PointsAmount. Was that intentional? Seems to me you've either gone too far or not far enough, depending on what it was for. (I don't know, for instance, whether you might have more than one 'pp' record per invoice - deliberately or accidentally.) John -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK Sent: 08 January 2006 12:35 To: 'Access Developers discussion and problem solving' Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com