How can I force the query to calculate the average of all records?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi !

I would like to create a query with 3 fields (columns).

First column: ID (primary key field)
Second column: Sugar cane species (duplicate records)
Third column: Average productivity for the respective sugar cane species
(numeric)

The duplicate records of the second row can't be removed, because they have
to preserve their identity, expressed by the first column.

Using "totals" for the query, the third column doesn't return the average
value of each sugar cane species, when I choose "mean"in the "sort" cell.

The value calculated in this third row is the "mean"value of the first two
records of each sugar cane species, instead of all records.

How can I force the query, to calculate in the third row the mean value of
all records for each specific sugar cane species?

Her a little example:

Original data
ID Sugar cane species Productivity index
1 A 1.7
2 A 1.2
3 A 1.0
4 B 1.4

Created query:
ID Sugar cane species Productivity index
1 A 1.7
2 A 1.7
3 A 1.0
4 B 1.4

Desired query:
ID Sugar cane species Productivity index
1 A 1.3
2 A 1.3
3 A 1.3
4 B 1.4

Somebody can help me ?

Sincerely
gabriel
 
gabriel said:
I would like to create a query with 3 fields (columns).

First column: ID (primary key field)
Second column: Sugar cane species (duplicate records)
Third column: Average productivity for the respective sugar cane
species (numeric)

The duplicate records of the second row can't be removed, because
they have to preserve their identity, expressed by the first column.

Using "totals" for the query, the third column doesn't return the
average value of each sugar cane species, when I choose "mean"in the
"sort" cell.

The value calculated in this third row is the "mean"value of the
first two records of each sugar cane species, instead of all records.

How can I force the query, to calculate in the third row the mean
value of all records for each specific sugar cane species?
Click to expand...
[snip]

First create a query that just produces the average for each species (do not
include the ID column). Then use that along with the original table as the
input to a second query that pulls the IDs from the table and the species and
averages from the query. You will need to join the table and query on the
species field.
 
One method would be to use a subquery in the select clause of your query

SELECT ID, [Sugar Cane Species]
, SELECT Avg([Productivity Index] FROM YourTable as Temp WHERE Temp.ID =
YourTable.ID) as AverageProductivity
FROM YourTable

A faster method would use two queries. First query name qProdIndexAvg

SELECT [Sugar Cane Species]
, Avg([Productivity Index]) as AvgProductivity
FROM YourTable
GROUP BY [Sugar Cane Species]

Now use that as if it were a table in the second query

SELECT YourTable.ID, YourTable.[Sugar Cane Species]
, AvgProductivity
FROM YourTable Inner Join qProdIndexAvg
ON YourTable.[Sugar Cane Species] = qProdIndexAvg.[Sugar Cane Species]

If you don't understand how to do this all in the SQL window, post back
for instructions on how to do this using Design View (the query grid)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Thank you Rick,

I could do the first query, which I converted into a table, but
wasn't able to join the two tables.
I'm not experienced with SQL syntaxe:

In SQL mode, I wrote following expression:

SELECT Consulta2005.VariedadedeCana,
FROM Consulta2005 INNER JOIN Step1
ON Consulta2005.VariedadedeCana = Step1.VariedadedeCana;

Table 1: Consulta2005
Table 2: Step1
Field with sugar cane species: Consulta2005.VariedadedeCana or
Step1.VariedadedeCana

Can you give me a little hint, what went wrong?

Thank you for your help!
daniel




"Rick Brandt" escreveu:
gabriel said:
I would like to create a query with 3 fields (columns).

First column: ID (primary key field)
Second column: Sugar cane species (duplicate records)
Third column: Average productivity for the respective sugar cane
species (numeric)

The duplicate records of the second row can't be removed, because
they have to preserve their identity, expressed by the first column.

Using "totals" for the query, the third column doesn't return the
average value of each sugar cane species, when I choose "mean"in the
"sort" cell.

The value calculated in this third row is the "mean"value of the
first two records of each sugar cane species, instead of all records.

How can I force the query, to calculate in the third row the mean
value of all records for each specific sugar cane species?
Click to expand...
[snip]

First create a query that just produces the average for each species (do not
include the ID column). Then use that along with the original table as the
input to a second query that pulls the IDs from the table and the species and
averages from the query. You will need to join the table and query on the
species field.
Click to expand...
 
gabriel said:
Thank you Rick,

I could do the first query, which I converted into a table,
Click to expand...

No, do not convert it to a table. Use the query as if it were a table and then
it never needs to be updated as records change.
but wasn't able to join the two tables.
I'm not experienced with SQL syntaxe:

In SQL mode, I wrote following expression:

SELECT Consulta2005.VariedadedeCana,
FROM Consulta2005 INNER JOIN Step1
ON Consulta2005.VariedadedeCana = Step1.VariedadedeCana;

Table 1: Consulta2005
Table 2: Step1
Field with sugar cane species: Consulta2005.VariedadedeCana or
Step1.VariedadedeCana

Can you give me a little hint, what went wrong?
Click to expand...

You need to include the average from the first query as well.
 
Hi Mr Spencer!
Thank you for your detailed answer, but I'm not experienced in SQL syntaxe,
so I will follow your advice to post a new question, how to do this query in
design view.

Sincerely
gabriel

"John Spencer" escreveu:
One method would be to use a subquery in the select clause of your query

SELECT ID, [Sugar Cane Species]
, SELECT Avg([Productivity Index] FROM YourTable as Temp WHERE Temp.ID =
YourTable.ID) as AverageProductivity
FROM YourTable

A faster method would use two queries. First query name qProdIndexAvg

SELECT [Sugar Cane Species]
, Avg([Productivity Index]) as AvgProductivity
FROM YourTable
GROUP BY [Sugar Cane Species]

Now use that as if it were a table in the second query

SELECT YourTable.ID, YourTable.[Sugar Cane Species]
, AvgProductivity
FROM YourTable Inner Join qProdIndexAvg
ON YourTable.[Sugar Cane Species] = qProdIndexAvg.[Sugar Cane Species]

If you don't understand how to do this all in the SQL window, post back
for instructions on how to do this using Design View (the query grid)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi !

I would like to create a query with 3 fields (columns).

First column: ID (primary key field)
Second column: Sugar cane species (duplicate records)
Third column: Average productivity for the respective sugar cane species
(numeric)

The duplicate records of the second row can't be removed, because they have
to preserve their identity, expressed by the first column.

Using "totals" for the query, the third column doesn't return the average
value of each sugar cane species, when I choose "mean"in the "sort" cell.

The value calculated in this third row is the "mean"value of the first two
records of each sugar cane species, instead of all records.

How can I force the query, to calculate in the third row the mean value of
all records for each specific sugar cane species?

Her a little example:

Original data
ID Sugar cane species Productivity index
1 A 1.7
2 A 1.2
3 A 1.0
4 B 1.4

Created query:
ID Sugar cane species Productivity index
1 A 1.7
2 A 1.7
3 A 1.0
4 B 1.4

Desired query:
ID Sugar cane species Productivity index
1 A 1.3
2 A 1.3
3 A 1.3
4 B 1.4

Somebody can help me ?

Sincerely
gabriel
Click to expand...
Click to expand...
 
Two query method.

Open a new query
-- SELECT YourTable
-- Select the fields Sugar Cane Species and Productivity Index
-- Select View: Totals from the menu
-- Change GROUP BY to Avg (Average) under Productivity Index
Save this query as qProdIndexAvg

Open a new query
-- Select Your table and qProdIndexAvg
-- Drag from Sugar Cane Species in the table to Sugar Cane Species in the
query to set up a link
-- SELECT the fields Id and Sugar Case Species from the table
-- Select AvgOfProductivity_Index from the query
Run the query.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

gabriel said:
Hi Mr Spencer!
Thank you for your detailed answer, but I'm not experienced in SQL
syntaxe,
so I will follow your advice to post a new question, how to do this query
in
design view.

Sincerely
gabriel

"John Spencer" escreveu:
One method would be to use a subquery in the select clause of your query

SELECT ID, [Sugar Cane Species]
, SELECT Avg([Productivity Index] FROM YourTable as Temp WHERE Temp.ID =
YourTable.ID) as AverageProductivity
FROM YourTable

A faster method would use two queries. First query name qProdIndexAvg

SELECT [Sugar Cane Species]
, Avg([Productivity Index]) as AvgProductivity
FROM YourTable
GROUP BY [Sugar Cane Species]

Now use that as if it were a table in the second query

SELECT YourTable.ID, YourTable.[Sugar Cane Species]
, AvgProductivity
FROM YourTable Inner Join qProdIndexAvg
ON YourTable.[Sugar Cane Species] = qProdIndexAvg.[Sugar Cane Species]

If you don't understand how to do this all in the SQL window, post back
for instructions on how to do this using Design View (the query grid)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi !

I would like to create a query with 3 fields (columns).

First column: ID (primary key field)
Second column: Sugar cane species (duplicate records)
Third column: Average productivity for the respective sugar cane
species
(numeric)

The duplicate records of the second row can't be removed, because they
have
to preserve their identity, expressed by the first column.

Using "totals" for the query, the third column doesn't return the
average
value of each sugar cane species, when I choose "mean"in the "sort"
cell.

The value calculated in this third row is the "mean"value of the first
two
records of each sugar cane species, instead of all records.

How can I force the query, to calculate in the third row the mean value
of
all records for each specific sugar cane species?

Her a little example:

Original data
ID Sugar cane species Productivity index
1 A 1.7
2 A 1.2
3 A 1.0
4 B 1.4

Created query:
ID Sugar cane species Productivity index
1 A 1.7
2 A 1.7
3 A 1.0
4 B 1.4

Desired query:
ID Sugar cane species Productivity index
1 A 1.3
2 A 1.3
3 A 1.3
4 B 1.4

Somebody can help me ?

Sincerely
gabriel
Click to expand...
Click to expand...
Click to expand...
 
Back
Top