Re: MSACCESS 2003 CREATE PROC
- From: MGFoster <me@xxxxxxxxxxx>
- Date: Wed, 22 Feb 2006 22:10:02 GMT
-----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+FFlQOCEAn0g7
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;
- References:
- MSACCESS 2003 CREATE PROC
- From: Matt Bob
- MSACCESS 2003 CREATE PROC
- Prev by Date: Re: Custom Collection Class woes
- Next by Date: Re: Attaching .xls file from a saved location
- Previous by thread: MSACCESS 2003 CREATE PROC
- Next by thread: Rebuild Form From Scratch?
- Index(es):
Relevant Pages
|
