472,364 Members | 2,043 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,364 software developers and data experts.

Jet Vs ADO aggregate function difference


just wasted a long time figuring out this and I figure if I post it
might save someone some pain!

Jet (DAO) will allow you to to use nested aggregate functions like
building blocks, e.g.:

SELECT
A,
sum(B) as Answer1,
Answer1 * 2 as DoubleAnswer
From
Table
GROUP ON
A;

If you save this query and run it from ADO
(e.g. ADORecordset.Open "qrySavedQuery", myConnection) you will get:

You tried to execute a query that does not include the specified
expression 'Answer1 * 2' as part of an aggregate function.

Yet opening the saved query normally or calling it from DAO will work
fine.

ADO requires you to write the query like this:

SELECT
A,
sum(B) as Answer1,
sum(B) * 2 as DoubleAnswer
From
Table
GROUP ON
A;

i.e. an aggregate function cannot refer to another aggregate function
in the same query.

Anyone else found this, or am I mis-reading cause and effect of problem
here?

Nov 9 '06 #1
0 1483

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: sausage31 | last post by:
I have a table as follows.... Device LotID Result1 Result2 Result3 aaa 1 5 10 15 bbb 1 2 4 6 aaa 2 ...
1
by: nfrodsham | last post by:
In Microsoft's help literature, it states: "You can filter out non-unique rows by using the DISTINCT option of an aggregate function" I am trying to do this in Access 2003 with the COUNT...
10
by: neb | last post by:
Dear member of the forum, Ms access has built-in aggregate function like: -Sum, Max, First, Avg, ... Is it possible to build user-defined aggregate? (if you have any clue, do not hesitate to...
2
by: Shannan Casteel via AccessMonster.com | last post by:
I have an invoice form with Parts and Labor as subforms. The parts section is based on a query that pulls data from 2 tables (tblParts & tblPartPerClaim) .. It has fields: ClaimID:...
12
by: Bill Moran | last post by:
Hey all. I've hit an SQL problem that I'm a bit mystified by. I have two different questions regarding this problem: why? and how do I work around it? The following query: SELECT GCP.id,...
1
by: Najib Abi Fadel | last post by:
Hi i have an ordered table of dates let's say: 1/1/2004 8/1/2004 15/1/2004 29/1/2004 5/2/2004 12/2/2004
5
by: BillCo | last post by:
I just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: ...
2
by: Ian825 | last post by:
I need help writing a function for a program that is based upon the various operations of a matrix and I keep getting a "non-aggregate type" error. My guess is that I need to dereference my...
1
by: Sandro997 | last post by:
Ok. I have a bit of a dilemma here. First, please consider the following function: CREATE OR REPLACE FUNCTION recent_lab(text, labs, treatments) RETURNS float8 AS $BODY$select...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.