can't use an aggregate function in an expression?

  • Thread starter Thread starter wannabe geek
  • Start date Start date
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?!!!!!!!
 
Try this ---
SELECT Packages.Package,
([Packages.BasePriceUS]+Sum([PackageModules].[Price])) AS Price
FROM Packages INNER.....
 
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...
 
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...
 
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...
 
Back
Top