Re: [MS_AccessPros] ODBC - Call falled. Not Sure why!

 

I agree with John regarding the filtering in the WHERE clause vs the HAVING clause. The ORDER BY clause should be last.

SELECT Count(ApplicantID) AS  Applications, Year(AppReceivedDate) AS Year_of_Application

FROM DBO.tblApplication

WHERE Year(AppReceivedDate)>=2008

GROUP BY Year(AppReceivedDate)

ORDER BY Year(AppReceivedDate);

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Thursday, March 23, 2017 10:10 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] ODBC - Call falled. Not Sure why!

 

How would I add a Order BY clause into this because I need it ordered by Year (oldest to newest). 

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."  

---

From: "Bill Mosca wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "Professionals, MS" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, March 22, 2017 3:21 PM
Subject: Re: [MS_AccessPros] ODBC - Call falled. Not Sure why!

 

Art - you might also try moving the WHERE clause to a HAVING clause. It might improve performance.

SELECT Count(ApplicantID) AS Applications
, Year(AppReceivedDate) AS Year_of_Application
FROM DBO.tblApplication
GROUP BY Year(AppReceivedDate);
HAVING Year(AppReceivedDate)>=2008

Regards,

Bill Mosca

---

From: "Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, March 22, 2017 1:57:11 PM
Subject: Re: [MS_AccessPros] ODBC - Call falled. Not Sure why!

 

John, 

Perfect, thank you. 

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."  

---

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, March 22, 2017 2:43 PM
Subject: Re: [MS_AccessPros] ODBC - Call falled. Not Sure why!

 

You could try a passthrough query:

SELECT Count(ApplicantID) AS  Applications, Year(AppReceivedDate) AS Year_of_Application

FROM DBO.tblApplication

WHERE Year(AppReceivedDate)>=2008

GROUP BY Year(AppReceivedDate);

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/

John Viescas Consulting www.viescas.com Welcome to the home of John Viescas Consulting. If you're at all interested in Microsoft Access or SQL Server, this is the place to be! Check out the book ...

 

(Paris, France)

On Mar 22, 2017, at 9:27 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Yes it is a SQL Server 2008. 

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   

---

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Wednesday, March 22, 2017 1:50 PM
Subject: Re: [MS_AccessPros] ODBC - Call falled. Not Sure why!

Art-

And what database stores that table?  SQL Server?

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Mar 22, 2017, at 8:42 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Yes it is. tblApplication is a linked table. 

With Warm Regards,

 

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265  Ext. 130

Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   

---

From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Wednesday, March 22, 2017 1:41 PM
Subject: Re: [MS_AccessPros] ODBC - Call falled. Not Sure why!

Art-

ODBC implies you're connecting to a back end like SQL Server.  Is that the case?  Is tblApplication a linked table?

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Mar 22, 2017, at 8:16 PM, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have the following query...

SELECT Count([tblApplication].[ApplicantID]) AS  Applications, Year([tblApplication].[AppReceivedDate]) AS [Year of Application]

FROM tblApplication

WHERE (((Year([tblApplication].[AppReceivedDate]))>=2008))

GROUP BY Year([tblApplication].[AppReceivedDate]);

I get ODBC-call failed... error. I am not sure what's wrong with it.

Thank you ,

Art Lorenzini

__._,_.___

---

Posted by: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (11)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___