Specified Expression/Aggreagate Function

  • Thread starter Thread starter dab1477
  • Start date Start date
D

dab1477

I am recieving the following error during the setup/running of a query:
"You tried to execute a query that does not include the psecified expression
'Part Number' as part of an aggregate funtion".
I am trying to calculate the following expression using a query based upon a
master table:
Expr 1: sum(3600/cycle Time)
The master table has the following headings:
Date Part Number Tool Number Cycle Time

Date, Part Number and Cycle Time can be repeated, but tool number is dynamic
for each part number.
EXAMPLE
Date Part Number Tool Number Cycle Time
3/20/2009 80257 1 48
3/20/2009 80257 3 48
3/20/2009 80257 4 52

I want the query to calculate Expr 1 for EACH of the Tool Number within a
specific Part number. My current query looks like this:
Date Part Number Tool Number Cycle Time Expr 1

where Expr 1 = sum(3600/cycle time)

Thanks in advance for the direction.
Stumped newbie,
dab1477
 
dab1477 said:
I am recieving the following error during the setup/running of a
query: "You tried to execute a query that does not include the
psecified expression 'Part Number' as part of an aggregate funtion".
I am trying to calculate the following expression using a query based
upon a master table:
Expr 1: sum(3600/cycle Time)
The master table has the following headings:
Date Part Number Tool Number Cycle Time

Date, Part Number and Cycle Time can be repeated, but tool number is
dynamic for each part number.
EXAMPLE
Date Part Number Tool Number Cycle Time
3/20/2009 80257 1 48
3/20/2009 80257 3 48
3/20/2009 80257 4 52

I want the query to calculate Expr 1 for EACH of the Tool Number
within a specific Part number. My current query looks like this:
Date Part Number Tool Number Cycle Time Expr 1

where Expr 1 = sum(3600/cycle time)
Click to expand...

To show us your query, you need to switch your query from Design View to
SQL View using the View menu, or the right-click context menu, or the
toolbar button. Copy/paste the sql statement from the SQL View window.

Did you use the toolbar button to turn on the Totals row?
What do you want the result of this query to look like?
 
dab1477 said:
I am recieving the following error during the setup/running of a
query: "You tried to execute a query that does not include the
psecified expression 'Part Number' as part of an aggregate funtion".
I am trying to calculate the following expression using a query based
upon a master table:
Expr 1: sum(3600/cycle Time)
The master table has the following headings:
Date Part Number Tool Number Cycle Time

Date, Part Number and Cycle Time can be repeated, but tool number is
dynamic for each part number.
EXAMPLE
Date Part Number Tool Number Cycle Time
3/20/2009 80257 1 48
3/20/2009 80257 3 48
3/20/2009 80257 4 52

I want the query to calculate Expr 1 for EACH of the Tool Number
within a specific Part number. My current query looks like this:
Date Part Number Tool Number Cycle Time Expr 1

where Expr 1 = sum(3600/cycle time)

Thanks in advance for the direction.
Stumped newbie,
dab1477
Click to expand...

You will not receive help unless you respond to our requests for further
information ...
 
Bob,
My apologies for posting late. I am juggling multiple projects. Thanks for
understanding. The SQL post is as follows:
SELECT Tbl_CurrentStateMaster.Date, Tbl_CurrentStateMaster.[Part Number],
Tbl_CurrentStateMaster.CS_Tool_Number,
Sum(3600/Tbl_CurrentStateMaster!CS_Cycle_Time) AS Expr1
FROM Tbl_CurrentStateMaster
ORDER BY Tbl_CurrentStateMaster.[Part Number];

I am not knowledgeable with SQL. I used Design view to create the query.

Thanks again.
 
Some generalities to begin with:
Sum() is an aggregate function designed to sum values contained in
multiple rows of a table or resultset. If it is the only column to be
returned in a query, it can be used without a GROUP BY clause:

Select Sum(somecolumn) as total from sometable where ...

If non-aggregated columns are also to be returned, the query MUST have a
GROUP BY clause and the non-aggregated fields MUST be included in the
GROUP BY clause.

So, to get rid of your error message, you need to change your query to
look like this:

SELECT Tbl_CurrentStateMaster.Date, Tbl_CurrentStateMaster.[Part
Number],
Tbl_CurrentStateMaster.CS_Tool_Number,
Sum(3600/Tbl_CurrentStateMaster.CS_Cycle_Time) AS Expr1
FROM Tbl_CurrentStateMaster
GROUP BY Tbl_CurrentStateMaster.Date, Tbl_CurrentStateMaster.[Part
Number],
Tbl_CurrentStateMaster.CS_Tool_Number
ORDER BY Tbl_CurrentStateMaster.[Part Number];

In Design View, this is done by click the Totals toolbar button.

I'm not sure this query will give you the results you are looking for.
Since you would be grouping by all three fields, your query would return
3 rows, because each has a different tool number. Of course, if your
data includes multiple rows for each tool number, then the aggregation
would be what you want it to be.

However, if your data is accurately represented by your example set,
i.e. one row for each tool number, and you actually want the query to
return 3 rows, one for each tool number, then I question why you need
the Sum() function in the first place. Wouldn't the following give you
the result you need?

SELECT Tbl_CurrentStateMaster.Date, Tbl_CurrentStateMaster.[Part
Number],
Tbl_CurrentStateMaster.CS_Tool_Number,
3600/Tbl_CurrentStateMaster.CS_Cycle_Time AS Expr1
FROM Tbl_CurrentStateMaster
ORDER BY Tbl_CurrentStateMaster.[Part Number];



Bob,
My apologies for posting late. I am juggling multiple projects.
Thanks for understanding. The SQL post is as follows:
SELECT Tbl_CurrentStateMaster.Date, Tbl_CurrentStateMaster.[Part
Number], Tbl_CurrentStateMaster.CS_Tool_Number,
Sum(3600/Tbl_CurrentStateMaster!CS_Cycle_Time) AS Expr1
FROM Tbl_CurrentStateMaster
ORDER BY Tbl_CurrentStateMaster.[Part Number];

I am not knowledgeable with SQL. I used Design view to create the
query.

Thanks again.

Bob Barrows said:
You will not receive help unless you respond to our requests for
further information ...
Click to expand...
Click to expand...
 
Thank you Bob. Your solution worked. As a newbie I am trying to understand
how it works, but it works. Thank you.

Bob Barrows said:
Some generalities to begin with:
Sum() is an aggregate function designed to sum values contained in
multiple rows of a table or resultset. If it is the only column to be
returned in a query, it can be used without a GROUP BY clause:

Select Sum(somecolumn) as total from sometable where ...

If non-aggregated columns are also to be returned, the query MUST have a
GROUP BY clause and the non-aggregated fields MUST be included in the
GROUP BY clause.

So, to get rid of your error message, you need to change your query to
look like this:

SELECT Tbl_CurrentStateMaster.Date, Tbl_CurrentStateMaster.[Part
Number],
Tbl_CurrentStateMaster.CS_Tool_Number,
Sum(3600/Tbl_CurrentStateMaster.CS_Cycle_Time) AS Expr1
FROM Tbl_CurrentStateMaster
GROUP BY Tbl_CurrentStateMaster.Date, Tbl_CurrentStateMaster.[Part
Number],
Tbl_CurrentStateMaster.CS_Tool_Number
ORDER BY Tbl_CurrentStateMaster.[Part Number];

In Design View, this is done by click the Totals toolbar button.

I'm not sure this query will give you the results you are looking for.
Since you would be grouping by all three fields, your query would return
3 rows, because each has a different tool number. Of course, if your
data includes multiple rows for each tool number, then the aggregation
would be what you want it to be.

However, if your data is accurately represented by your example set,
i.e. one row for each tool number, and you actually want the query to
return 3 rows, one for each tool number, then I question why you need
the Sum() function in the first place. Wouldn't the following give you
the result you need?

SELECT Tbl_CurrentStateMaster.Date, Tbl_CurrentStateMaster.[Part
Number],
Tbl_CurrentStateMaster.CS_Tool_Number,
3600/Tbl_CurrentStateMaster.CS_Cycle_Time AS Expr1
FROM Tbl_CurrentStateMaster
ORDER BY Tbl_CurrentStateMaster.[Part Number];



Bob,
My apologies for posting late. I am juggling multiple projects.
Thanks for understanding. The SQL post is as follows:
SELECT Tbl_CurrentStateMaster.Date, Tbl_CurrentStateMaster.[Part
Number], Tbl_CurrentStateMaster.CS_Tool_Number,
Sum(3600/Tbl_CurrentStateMaster!CS_Cycle_Time) AS Expr1
FROM Tbl_CurrentStateMaster
ORDER BY Tbl_CurrentStateMaster.[Part Number];

I am not knowledgeable with SQL. I used Design view to create the
query.

Thanks again.

Bob Barrows said:
dab1477 wrote:
I am recieving the following error during the setup/running of a
query: "You tried to execute a query that does not include the
psecified expression 'Part Number' as part of an aggregate funtion".
I am trying to calculate the following expression using a query
based upon a master table:
Expr 1: sum(3600/cycle Time)
The master table has the following headings:
Date Part Number Tool Number Cycle Time

Date, Part Number and Cycle Time can be repeated, but tool number is
dynamic for each part number.
EXAMPLE
Date Part Number Tool Number Cycle Time
3/20/2009 80257 1 48
3/20/2009 80257 3 48
3/20/2009 80257 4 52

I want the query to calculate Expr 1 for EACH of the Tool Number
within a specific Part number. My current query looks like this:
Date Part Number Tool Number Cycle Time Expr 1

where Expr 1 = sum(3600/cycle time)

Thanks in advance for the direction.
Stumped newbie,
dab1477

You will not receive help unless you respond to our requests for
further information ...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top