Calculate Values - Aggregate function error 1

[rccline]

I am trying to run a query to calculate values. I can add as many product values:

Expr1:[field1]*[field2]

into the query grid as I want and get the query to run.

And I can create a single query for a sum
Expr1: sum([field1]+[field2])

But if I try and create a query with both product and sum calculations I get an "aggregate function" error:

Error Message: "You tried to execute a query that does not include the specified expression 'fieldX' as part of an aggregate function."

If I remove 'fieldx' and only include the calculations in the query, it gives the "aggregate function" error for the first field in the first product calculation and the error:

Error Message: "You tried to execute a query that does not include the specified expression 'field1' as part of an aggregate function."


Could someone explain what could be wrong? Can I create a query with both product and sum calculations?

Thanks.

Robert

 

[dhookom]

Post your SQL view.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rccline]

The following code produces 'LineNo' as the aggregate funtion error:

SELECT qry_TractNets.[LINENO], qry_TractNets.TractNet, qry_TractNets.ROYALTY, 

     [tractnet]*[Royalty] AS TotalNMA, 
     [DefectiveNet]*[Royalty] AS TotalDefectiveNMA, 
      Sum([tractnet]+[defectivenet]) AS Total

FROM qry_TractNets;

If I remove 'LineNo' from the query grid, I get 'TractNet' as the aggregate function error.

 

[rccline]

If I remove the sum function from the query, the Alias 'Total' only returns defectivenet, not the sum of [tractnet]+[defectivenet]

SELECT qry_TractNets.[LINENO], qry_TractNets.TractNet, qry_TractNets.ROYALTY, 

     [tractnet]*[Royalty] AS TotalNMA, 
     [DefectiveNet]*[Royalty] AS TotalDefectiveNMA, 
     [tractnet]+[defectivenet] AS Total

FROM qry_TractNets;

 

[dhookom]

You need to make up your mind if you want to group by or aggregate every field in your SELECT clause. You could use:

SELECT qry_TractNets.[LINENO], qry_TractNets.TractNet, qry_TractNets.ROYALTY, 

     [tractnet]*[Royalty] AS TotalNMA, 
     [DefectiveNet]*[Royalty] AS TotalDefectiveNMA, 
      Sum([tractnet]+[defectivenet]) AS Total

FROM qry_TractNets;
GROUP BY  [LINENO], TractNet, ROYALTY,  
     [DefectiveNet];

Do you want to display only totals or details and totals? If you are displaying the results in a report, you can use Group Headers and/or Footers to display totals.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rccline]

I am just trying to display totals. I am not trying to group. I got to using the sum because I couldn't get the simple formula below to work:

[tractnet]+[defectivenet] AS Total

or

Expr1:[tractnet]+[defectivenet]

 

[dhookom]

Are you suggesting that you tried this and it didn't work?

SELECT qry_TractNets.[LINENO], qry_TractNets.TractNet,
     qry_TractNets.ROYALTY, 
     [tractnet]*[Royalty] AS TotalNMA, 
     [DefectiveNet]*[Royalty] AS TotalDefectiveNMA, 
    [tractnet]+[defectivenet] AS Total
FROM qry_TractNets;

If it didn't work, what error or miscalculation did you get?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rccline]

(1) Only the DefectiveNet appears in Total. TractNet is not summed.

(2) The products of Royalty times either:
(a) tractnet, or
(b) DefectiveNet are calculated.

 

[dhookom]

Are your points observations or requests/requirements. Can you maybe enter some sample records and how you want to display the records in your query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rccline]

I'm still stuck:

The data looks Like this:

LineNo tractnet defectnet
1 123.44 22.11
2 223.33 122.00
3 400.00 2.22

Here's what I try:

SELECT tblTracts.LineNo, tblTracts.TractNet, tblTracts.DefectiveNet,[tractnet]+[defectivenet] As TotalNet
FROM tblTracts;


I want the extra field, [TotalNet] to appear in my query with the sum of tractnet + defectnet

Thanks.

Robert

 

[lespaul]

and when you try to run THIS query:

SELECT tblTracts.LineNo, tblTracts.TractNet, tblTracts.DefectiveNet,[tractnet]+[defectivenet] As TotalNet
FROM tblTracts;

what's the error message?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[rccline]

Ah ha!

TotalNet is ONLY calculated if there is a number in DefectiveNet field.

Therefore, TractNet field must be populated with spaces.

I think I should run an update query to Null for all TractNet " "

No?

 

[rccline]

Nope. I tried to query for " " and got no results.
Moving my cursor into the empty cells, it appears they are empty.

 

[lespaul]

WHat about:

SELECT tblTracts.LineNo, tblTracts.TractNet, tblTracts.DefectiveNet,NZ([tractnet], 0)+NZ([defectivenet],0) As TotalNet
FROM tblTracts;

 

[rccline]

YES! This works. THANK YOU all for responding. Thank YOU Lespaul for the solution. I had struggled with that question for some time and couldn't figure it out.

Robert

 

[lespaul]

glad to help and thanks for the star!

leslie