I stand corrected, John. So why is a HAVING clause ever necessary if it is that inefficient?
Regards,
Bill
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Wednesday, March 22, 2017 2:33 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] ODBC - Call falled. Not Sure why!
Umm. I don't think so, Bill. The WHERE clause filters rows first, THEN applies the grouping. Putting it in the HAVING clause causes it to GROUP the entire table first, then filter it.
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
(Paris, France)
On Mar 22, 2017, at 10:20 PM, Bill Mosca wrmosca@comcast.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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
(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
(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
(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: "Bill Mosca" <wrmosca@comcast.net>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (13) |
Tidak ada komentar:
Posting Komentar