SQL Average Function help

[John Michl]

How does one return the average of fields in a VBA sql statement? The
select statement below works fine if pasted into a Query Analyzer window in
SQL Enterprise manager (removing the VBA specific stuff, of course) but does
not work in Excel. If I remove the phrase
AVG(OccurenceSummary.DispenserQty) AS 'Average Dispensers', it works fine
in Excel.

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL
Server;SERVER=LINCOLN;UID=JMichl;APP=Microsoft Office
XP;WSID=JMICHL;DATABASE=GPTest;Trusted_Connection=Yes" _
, Destination:=Range("A62"))
.CommandText = Array( _
"SELECT AVG(OccurenceSummary.DispenserQty) AS 'Average Dispensers',
OccurenceSummary.MigrationType, " _
"Count(OccurenceSummary.OccurenceID) AS 'Occurences',
Sum(OccurenceSummary.DispenserQty) AS 'Total Dispensers'" _
& Chr(13) & "" & Chr(10) & "FROM GPTest.dbo.OccurenceSummary
OccurenceSummary" & Chr(13) & "" & Chr(10) & "GROUP BY
OccurenceSummary.MigrationType" )

End With

Thanks
- John Michl
www.JohnMichl.com

 

[Tim Williams]

John,

I'd suggest building the SQL statement in a variable and then using it
in the commandtext parameter.

The chr(13) & chr(10) aren't required: a space is all that's needed
there.

The advantage of using a string variable is you can debug.print it and
paste it into Query Analyzer to check it's what you expect.

Tim.

 

[onedaywhen]

You've kinda answered your own question there: the problem is the
aliases. If only you'd made the leap of faith to check MSDN:

Microsoft Knowledge Base Article - 298955
XL: Using a Field Alias in Query Does Not Work with Some Third-Party
Databases
http://support.microsoft.com/default.aspx?scid=kb;en-us;298955

 

[John Michl]

Thanks. I figured that out last night with the alias. I had checked
Microsoft but since I didn't figure it had something to do with the alias, I
didn't use the correct search terms.

Thanks for the link.

- John Michl

 

[John Michl]

Unfortunately, the hotfix didn't do the trick.

It appears that the problem isn't related to a particular function (as I
originally thought) or a particular alias but rather the number of aliases
added to the string. I can have two in the select statement but the third
causes the error. If I add additional fields but remove the alias so there
are no more than two in the string, it works ok.

Any other ideas?

- John Michl

 

[onedaywhen]

I can have two [aliases] in the select statement but the third
causes the error. If I add additional fields (sic) but remove the alias so
there are no more than two in the string, it works ok.

Tommy Cooper once said, 'I went to the doctor and I said, "It hurts
when I do that", he said, "Well, don't do it."'

--