MSACCESS 2003 CREATE PROC

[Matt Bob]

I have a vba function im my database to create stored queries in
access. It all works fine except when I start getting into more complex
queries that use aggragate functions.

For example, if I type into a query:

Select tbl1.fieldA, tbl1.fieldB, tbl2.fieldC, sum(tbl2.fieldd) as
sumOfD, iif(sumOfD=0,0,tbl1.fieldB/sumOfD) as ratio FROM tbl1 LEFT JOIN
tbl2 on tbl1.fieldc=tbl2.fieldc WHERE tbl2.fieldE>100 GROUP BY
sum(tbl2.fieldd);

it runs fine, but if I pass the string to this fuction to create the
procedure, it says it needs:
"iif(sumOfD=0,0,tbl1.fieldB/sumOfD)" in the GROUP BY clause. If I try
adding it, it says that it should not be part of the GROUP BY clause.

So.... my question is, is there any way to get by this?

I simply use
CurrentProject.Connection.Execute SQL

to run the SQL, and my SQL string looks like:
CREATE PROC [QueryName] as SelectQuerySQL;

thanks for any help

Matt Bob

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The query is incorrect in the GROUP BY clause. Let's try some ANSI 92
syntax instead of JET's syntax (Using an aggregate alias in an
expression):

SELECT T1.fieldA,
T1.fieldB,
T2.fieldC,
IIf(SUM(T2.fieldD)=0,0,T1.fieldB/SUM(T2.fieldD)) as ratio

FROM tbl1 As T1
LEFT JOIN
tbl2 As T2
ON T1.fieldc=T2.fieldc

WHERE T2.fieldE>100

GROUP BY T1.fieldA, T1.fieldB, T2.fieldC

If this fails 'cuz it wants T1.FieldB in the GROUP BY clause, in the
Query's Design column grid, try setting the ratio column's "Total" row
to "Expression."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/zhO4echKqOuFEgEQJcQACg1KZ+tVjD4HsjRHXgEfb+FFlQOCEA n0g7
w9K8+atUA3y6ik+umG81upgU
=5QpP
-----END PGP SIGNATURE-----
Matt Bob wrote:

I have a vba function im my database to create stored queries in
access. It all works fine except when I start getting into more complex
queries that use aggragate functions.

For example, if I type into a query:

Select tbl1.fieldA, tbl1.fieldB, tbl2.fieldC, sum(tbl2.fieldd) as
sumOfD, iif(sumOfD=0,0,tbl1.fieldB/sumOfD) as ratio FROM tbl1 LEFT JOIN
tbl2 on tbl1.fieldc=tbl2.fieldc WHERE tbl2.fieldE>100 GROUP BY
sum(tbl2.fieldd);

it runs fine, but if I pass the string to this fuction to create the
procedure, it says it needs:
"iif(sumOfD=0,0,tbl1.fieldB/sumOfD)" in the GROUP BY clause. If I try
adding it, it says that it should not be part of the GROUP BY clause.

So.... my question is, is there any way to get by this?

I simply use
CurrentProject.Connection.Execute SQL

to run the SQL, and my SQL string looks like:
CREATE PROC [QueryName] as SelectQuerySQL;