can't use an aggregate function in an expression?

W

wannabe geek

I am using MS Access 2007. My SQL reads as follows:

SELECT Packages.Package,
[Packages.BasePriceUS]+Sum([PackageModules].[Price]) AS Price
FROM Packages INNER JOIN (PackageModules INNER JOIN AnalysisResults ON
PackageModules.Package = AnalysisResults.Package) ON Packages.Package =
PackageModules.Package
WHERE (((PackageModules.Module)="Module1" Or
(PackageModules.Module)="Module2" Or (PackageModules.Module)="Module3"))
GROUP BY Packages.Package;

I want to add Packages.Price to the sum of PackageModules.Price where
PackageModules.Package equals Packages.Package. (in other words, contains a
one-to-many relationship that does not technically exist)

I keep getting the error:
'You tried to execute a query that does not include the specified expression
'[Packages.BasePriceUS]+Sum([PackageModules].[Price])' as part of an
aggregate function'
yet when I include it in an aggregate function I get:
'Cannot have aggregate function in {clause or function}'
I think my Sum function is the problem.

I am using the query designer but I will also accept SQL input.

Can anyone help?!!!!!!!
 
K

KARL DEWEY

Try this ---
SELECT Packages.Package,
([Packages.BasePriceUS]+Sum([PackageModules].[Price])) AS Price
FROM Packages INNER.....
 
W

wannabe geek

I am still getting the exact same error.

I don't have a one-to-many relationship because the one side is not the
primary key. Would a one-to-many realtionship help?

--

Wannabe Geek


KARL DEWEY said:
Try this ---
SELECT Packages.Package,
([Packages.BasePriceUS]+Sum([PackageModules].[Price])) AS Price
FROM Packages INNER.....


--
Build a little, test a little.


wannabe geek said:
I am using MS Access 2007. My SQL reads as follows:

SELECT Packages.Package,
[Packages.BasePriceUS]+Sum([PackageModules].[Price]) AS Price
FROM Packages INNER JOIN (PackageModules INNER JOIN AnalysisResults ON
PackageModules.Package = AnalysisResults.Package) ON Packages.Package =
PackageModules.Package
WHERE (((PackageModules.Module)="Module1" Or
(PackageModules.Module)="Module2" Or (PackageModules.Module)="Module3"))
GROUP BY Packages.Package;

I want to add Packages.Price to the sum of PackageModules.Price where
PackageModules.Package equals Packages.Package. (in other words, contains a
one-to-many relationship that does not technically exist)

I keep getting the error:
'You tried to execute a query that does not include the specified expression
'[Packages.BasePriceUS]+Sum([PackageModules].[Price])' as part of an
aggregate function'
yet when I include it in an aggregate function I get:
'Cannot have aggregate function in {clause or function}'
I think my Sum function is the problem.

I am using the query designer but I will also accept SQL input.

Can anyone help?!!!!!!!
Click to expand...
Click to expand...
 
K

KenSheridan via AccessMonster.com

You can sum the package modules prices for the current package in a subquery,
e.g.

SELECT Package, BasePriceUS +
NZ(SELECT SUM(Price)
FROM PackageModules
WHERE PackageModules.Package = Packages.Package
AND Module IN("Module1", "Module2", "Module3")),0)
AS GrossPrice
FROM Packages;

I've not included the AnalysisResults table as I don't see that it was
playing any part in your original query (more on this below). The NZ function
is used here to return a zero if the subquery returns no rows for a
particular package. Otherwise it would return a Null and the GrossPrice
would then be Null as Anything + Null = Null. So in those cases the
BasePriceUS and Grossprice values would be the same. If, on the other hand
you wanted to exclude these rows then you could use:

SELECT DISTINCT Packages.Package, Packages.BasePriceUS +
(SELECT SUM(Price)
FROM PackageModules
WHERE PackageModules.Package = Packages.Package
AND Module IN("Module1", "Module2", "Module3"))
AS GrossPrice
FROM Packages INNER JOIN PackageModules
ON PackageModules.Package = Packages.Package
WHERE Module IN("Module1", "Module2", "Module3");

The NZ function is not needed in this case as the subquery cannot return a
Null.

The only reason I can see why the AnalysisResults table might have been
included in the original query is to exclude any rows without a match in that
table. If this was the reason then this could also be included in the outer
query:

SELECT DISTINCT Packages.Package, Packages.BasePriceUS +
(SELECT SUM(Price)
FROM PackageModules
WHERE PackageModules.Package = Packages.Package
AND Module IN("Module1", "Module2", "Module3"))
AS GrossPrice
FROM Packages INNER JOIN (PackageModules INNER JOIN AnalysisResults
ON PackageModules.Package = AnalysisResults.Package)
ON Packages.Package = PackageModules.Package
WHERE Module IN("Module1", "Module2", "Module3");

Ken Sheridan
Stafford, England

wannabe said:
I am using MS Access 2007. My SQL reads as follows:

SELECT Packages.Package,
[Packages.BasePriceUS]+Sum([PackageModules].[Price]) AS Price
FROM Packages INNER JOIN (PackageModules INNER JOIN AnalysisResults ON
PackageModules.Package = AnalysisResults.Package) ON Packages.Package =
PackageModules.Package
WHERE (((PackageModules.Module)="Module1" Or
(PackageModules.Module)="Module2" Or (PackageModules.Module)="Module3"))
GROUP BY Packages.Package;

I want to add Packages.Price to the sum of PackageModules.Price where
PackageModules.Package equals Packages.Package. (in other words, contains a
one-to-many relationship that does not technically exist)

I keep getting the error:
'You tried to execute a query that does not include the specified expression
'[Packages.BasePriceUS]+Sum([PackageModules].[Price])' as part of an
aggregate function'
yet when I include it in an aggregate function I get:
'Cannot have aggregate function in {clause or function}'
I think my Sum function is the problem.

I am using the query designer but I will also accept SQL input.

Can anyone help?!!!!!!!
Click to expand...
 
K

KARL DEWEY

I think your problem is with the
([Packages.BasePriceUS]+Sum([PackageModules].[Price]) AS Price in that
you are trying to add a single record to a sum of records.

Maybe this will work --
(First([Packages.BasePriceUS])+Sum([PackageModules].[Price]) AS Price

Otherwise I think you will need to separetly sum the field and in another
query or subquery add on the BasePriceUS.

What role does the table AnalysisResults play in this? I do not see how it
is used.

--
Build a little, test a little.


wannabe geek said:
I am still getting the exact same error.

I don't have a one-to-many relationship because the one side is not the
primary key. Would a one-to-many realtionship help?

--

Wannabe Geek


KARL DEWEY said:
Try this ---
SELECT Packages.Package,
([Packages.BasePriceUS]+Sum([PackageModules].[Price])) AS Price
FROM Packages INNER.....


--
Build a little, test a little.


wannabe geek said:
I am using MS Access 2007. My SQL reads as follows:

SELECT Packages.Package,
[Packages.BasePriceUS]+Sum([PackageModules].[Price]) AS Price
FROM Packages INNER JOIN (PackageModules INNER JOIN AnalysisResults ON
PackageModules.Package = AnalysisResults.Package) ON Packages.Package =
PackageModules.Package
WHERE (((PackageModules.Module)="Module1" Or
(PackageModules.Module)="Module2" Or (PackageModules.Module)="Module3"))
GROUP BY Packages.Package;

I want to add Packages.Price to the sum of PackageModules.Price where
PackageModules.Package equals Packages.Package. (in other words, contains a
one-to-many relationship that does not technically exist)

I keep getting the error:
'You tried to execute a query that does not include the specified expression
'[Packages.BasePriceUS]+Sum([PackageModules].[Price])' as part of an
aggregate function'
yet when I include it in an aggregate function I get:
'Cannot have aggregate function in {clause or function}'
I think my Sum function is the problem.

I am using the query designer but I will also accept SQL input.

Can anyone help?!!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
W

wannabe geek

Thanks, although this is not what need, even though it gets rid of the error.
I'll try changing relationships around or subquerying.
By the way, AnalysisResults is intended to be the results of this query when
I perfect it and change it to an update or append query. It has no input.

--

Wannabe Geek


KARL DEWEY said:
I think your problem is with the
([Packages.BasePriceUS]+Sum([PackageModules].[Price]) AS Price in that
you are trying to add a single record to a sum of records.

Maybe this will work --
(First([Packages.BasePriceUS])+Sum([PackageModules].[Price]) AS Price

Otherwise I think you will need to separetly sum the field and in another
query or subquery add on the BasePriceUS.

What role does the table AnalysisResults play in this? I do not see how it
is used.

--
Build a little, test a little.


wannabe geek said:
I am still getting the exact same error.

I don't have a one-to-many relationship because the one side is not the
primary key. Would a one-to-many realtionship help?

--

Wannabe Geek


KARL DEWEY said:
Try this ---
SELECT Packages.Package,
([Packages.BasePriceUS]+Sum([PackageModules].[Price])) AS Price
FROM Packages INNER.....


--
Build a little, test a little.


:

I am using MS Access 2007. My SQL reads as follows:

SELECT Packages.Package,
[Packages.BasePriceUS]+Sum([PackageModules].[Price]) AS Price
FROM Packages INNER JOIN (PackageModules INNER JOIN AnalysisResults ON
PackageModules.Package = AnalysisResults.Package) ON Packages.Package =
PackageModules.Package
WHERE (((PackageModules.Module)="Module1" Or
(PackageModules.Module)="Module2" Or (PackageModules.Module)="Module3"))
GROUP BY Packages.Package;

I want to add Packages.Price to the sum of PackageModules.Price where
PackageModules.Package equals Packages.Package. (in other words, contains a
one-to-many relationship that does not technically exist)

I keep getting the error:
'You tried to execute a query that does not include the specified expression
'[Packages.BasePriceUS]+Sum([PackageModules].[Price])' as part of an
aggregate function'
yet when I include it in an aggregate function I get:
'Cannot have aggregate function in {clause or function}'
I think my Sum function is the problem.

I am using the query designer but I will also accept SQL input.

Can anyone help?!!!!!!!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help Needed: Create Sum in Report Footer based on aggregate expression 1
Error - CANNOT HAVE AGGREGATE FUNCTION IN EXPRESSION 4
Help with: ...query that does not include specified expression ....as part of an aggregate function. 3
Aggregate Query in Access 2007 1
How do I add a subquery and field in an aggregate query 1
How to fix formula to avoid divide/zero and aggregate issue. 5
Invalid use of null in query expression 3
Specified Expression/Aggregate Funciton error 5

Top