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

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

aggregate function error 1
Help Needed: Create Sum in Report Footer based on aggregate expression 1
Error - CANNOT HAVE AGGREGATE FUNCTION IN EXPRESSION 4
Aggregate Query in Access 2007 1
Help with: ...query that does not include specified expression ....as part of an aggregate function. 3
Invalid use of null in query expression 3
Aggregate Expression error. 3
Aggregate function 4

Top