-----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:[color=blue]
> 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;[/color]
MSACCESS 2003 CREATE PROC
