Average

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

Guest

I have data in a table as follows:

Company Location Number
Comp A CA 3
Comp A WA 3
Comp B AZ 4
Comp C TX 5
Comp C IL 5
Comp C NY 5

The will always be only one unique "Number" value for each Company,
regardless of how many locations are present. What I am trying to do is
create another column that will average the "Number" field, but I only want
the average to be calculated based off of the one unique Number per Company.
So, for example, in the data above the average would be calculated as (3+4+5)
/ 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in Access, any
help would be appreciated.
 
1. Create a Group by query that includes ony the two relevent fields

SELECT TableName.Company, TableName.Number
FROM TableName
GROUP BY TableName.Company, TableName.Number

Save it, for my example let say it named QueryName

2. You can use code
DSum("[Number]","[QueryName]") / DCount("*","[QueryName]")

3. Or use another query that based on query 1, QueryName

SELECT Count(QueryName.Company) AS CountOfCompany, Sum(QueryName.Number) AS
SumOfNumber, SumOfNumber/CountOfCompany As AvgField
FROM QueryName
 
Hi, Jason.

Number is a Reserved word and should be changed to something else. Avoid
Reserved words and always use alphanumeric characters and the underscore
character only for your names to avoid chasing unnecessary bugs. For lists
of Reserved words to avoid, please see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335
So, for example, in the data above the average would be calculated as (3+4+5)
/ 3 = 4
Click to expand...

Try:

SELECT AVG([Number]) AS AvgNum
FROM (SELECT [Number]
FROM tblCompanyNums
GROUP BY Company, [Number]);

.. . . where tblCompanyNums is the name of the table and [Number] is the
numeric field.
as opposed to (3+3+4+5+5+5) / 5 = 5.
Click to expand...

Actually, that should read (3+3+4+5+5+5) / 6 = 4.167 (or 4 if it's just one
significant figure).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
 
Sorry
For the third part I should have used the AVG

SELECT Avg(QueryName.Number) AS AvgOfNumbe
FROM QueryName

--
Good Luck
BS"D


Ofer Cohen said:
1. Create a Group by query that includes ony the two relevent fields

SELECT TableName.Company, TableName.Number
FROM TableName
GROUP BY TableName.Company, TableName.Number

Save it, for my example let say it named QueryName

2. You can use code
DSum("[Number]","[QueryName]") / DCount("*","[QueryName]")

3. Or use another query that based on query 1, QueryName

SELECT Count(QueryName.Company) AS CountOfCompany, Sum(QueryName.Number) AS
SumOfNumber, SumOfNumber/CountOfCompany As AvgField
FROM QueryName

--
Good Luck
BS"D


Jason said:
I have data in a table as follows:

Company Location Number
Comp A CA 3
Comp A WA 3
Comp B AZ 4
Comp C TX 5
Comp C IL 5
Comp C NY 5

The will always be only one unique "Number" value for each Company,
regardless of how many locations are present. What I am trying to do is
create another column that will average the "Number" field, but I only want
the average to be calculated based off of the one unique Number per Company.
So, for example, in the data above the average would be calculated as (3+4+5)
/ 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in Access, any
help would be appreciated.
Click to expand...
Click to expand...
 
Sorry, but I wrote my answer to qickly
Also stage 2 can be using code

DAvg("Number","QueryName")
============================

But looking at Gunny's solution, is a much shorter solution

--
Good Luck
BS"D


Ofer Cohen said:
1. Create a Group by query that includes ony the two relevent fields

SELECT TableName.Company, TableName.Number
FROM TableName
GROUP BY TableName.Company, TableName.Number

Save it, for my example let say it named QueryName

2. You can use code
DSum("[Number]","[QueryName]") / DCount("*","[QueryName]")

3. Or use another query that based on query 1, QueryName

SELECT Count(QueryName.Company) AS CountOfCompany, Sum(QueryName.Number) AS
SumOfNumber, SumOfNumber/CountOfCompany As AvgField
FROM QueryName

--
Good Luck
BS"D


Jason said:
I have data in a table as follows:

Company Location Number
Comp A CA 3
Comp A WA 3
Comp B AZ 4
Comp C TX 5
Comp C IL 5
Comp C NY 5

The will always be only one unique "Number" value for each Company,
regardless of how many locations are present. What I am trying to do is
create another column that will average the "Number" field, but I only want
the average to be calculated based off of the one unique Number per Company.
So, for example, in the data above the average would be calculated as (3+4+5)
/ 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in Access, any
help would be appreciated.
Click to expand...
Click to expand...
 
2. You can use code
DSum("[Number]","[QueryName]") / DCount("*","[QueryName]")
Click to expand...

And:

DAvg("[Number]","[QueryName]")

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


Ofer Cohen said:
1. Create a Group by query that includes ony the two relevent fields

SELECT TableName.Company, TableName.Number
FROM TableName
GROUP BY TableName.Company, TableName.Number

Save it, for my example let say it named QueryName

2. You can use code
DSum("[Number]","[QueryName]") / DCount("*","[QueryName]")

3. Or use another query that based on query 1, QueryName

SELECT Count(QueryName.Company) AS CountOfCompany, Sum(QueryName.Number)
AS
SumOfNumber, SumOfNumber/CountOfCompany As AvgField
FROM QueryName

--
Good Luck
BS"D


Jason said:
I have data in a table as follows:

Company Location Number
Comp A CA 3
Comp A WA 3
Comp B AZ 4
Comp C TX 5
Comp C IL 5
Comp C NY 5

The will always be only one unique "Number" value for each Company,
regardless of how many locations are present. What I am trying to do is
create another column that will average the "Number" field, but I only
want
the average to be calculated based off of the one unique Number per
Company.
So, for example, in the data above the average would be calculated as
(3+4+5)
/ 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in Access,
any
help would be appreciated.
Click to expand...
Click to expand...
 
I know, It's pass midnight here and I'm beyond bedtime.
I think that was the sign to go to bed, but thanks for the remark, and I do
like your approach better.

--
Good Luck
BS"D


'69 Camaro said:
2. You can use code
DSum("[Number]","[QueryName]") / DCount("*","[QueryName]")
Click to expand...

And:

DAvg("[Number]","[QueryName]")

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


Ofer Cohen said:
1. Create a Group by query that includes ony the two relevent fields

SELECT TableName.Company, TableName.Number
FROM TableName
GROUP BY TableName.Company, TableName.Number

Save it, for my example let say it named QueryName

2. You can use code
DSum("[Number]","[QueryName]") / DCount("*","[QueryName]")

3. Or use another query that based on query 1, QueryName

SELECT Count(QueryName.Company) AS CountOfCompany, Sum(QueryName.Number)
AS
SumOfNumber, SumOfNumber/CountOfCompany As AvgField
FROM QueryName

--
Good Luck
BS"D


Jason said:
I have data in a table as follows:

Company Location Number
Comp A CA 3
Comp A WA 3
Comp B AZ 4
Comp C TX 5
Comp C IL 5
Comp C NY 5

The will always be only one unique "Number" value for each Company,
regardless of how many locations are present. What I am trying to do is
create another column that will average the "Number" field, but I only
want
the average to be calculated based off of the one unique Number per
Company.
So, for example, in the data above the average would be calculated as
(3+4+5)
/ 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in Access,
any
help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Hi, Ofer.

Thanks. But the good news is that every suggestion works, so any of them
can be used in a query or as part of the expression in a control property or
VBA code.

G'night!
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


Ofer Cohen said:
I know, It's pass midnight here and I'm beyond bedtime.
I think that was the sign to go to bed, but thanks for the remark, and I
do
like your approach better.

--
Good Luck
BS"D


'69 Camaro said:
2. You can use code
DSum("[Number]","[QueryName]") / DCount("*","[QueryName]")
Click to expand...

And:

DAvg("[Number]","[QueryName]")

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


Ofer Cohen said:
1. Create a Group by query that includes ony the two relevent fields

SELECT TableName.Company, TableName.Number
FROM TableName
GROUP BY TableName.Company, TableName.Number

Save it, for my example let say it named QueryName

2. You can use code
DSum("[Number]","[QueryName]") / DCount("*","[QueryName]")

3. Or use another query that based on query 1, QueryName

SELECT Count(QueryName.Company) AS CountOfCompany,
Sum(QueryName.Number)
AS
SumOfNumber, SumOfNumber/CountOfCompany As AvgField
FROM QueryName

--
Good Luck
BS"D


:

I have data in a table as follows:

Company Location Number
Comp A CA 3
Comp A WA 3
Comp B AZ 4
Comp C TX 5
Comp C IL 5
Comp C NY 5

The will always be only one unique "Number" value for each Company,
regardless of how many locations are present. What I am trying to do
is
create another column that will average the "Number" field, but I only
want
the average to be calculated based off of the one unique Number per
Company.
So, for example, in the data above the average would be calculated as
(3+4+5)
/ 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in
Access,
any
help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Thanks to you both, I'll give it a try.


'69 Camaro said:
Hi, Jason.

Number is a Reserved word and should be changed to something else. Avoid
Reserved words and always use alphanumeric characters and the underscore
character only for your names to avoid chasing unnecessary bugs. For lists
of Reserved words to avoid, please see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335
So, for example, in the data above the average would be calculated as (3+4+5)
/ 3 = 4
Click to expand...

Try:

SELECT AVG([Number]) AS AvgNum
FROM (SELECT [Number]
FROM tblCompanyNums
GROUP BY Company, [Number]);

. . . where tblCompanyNums is the name of the table and [Number] is the
numeric field.
as opposed to (3+3+4+5+5+5) / 5 = 5.
Click to expand...

Actually, that should read (3+3+4+5+5+5) / 6 = 4.167 (or 4 if it's just one
significant figure).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.


Jason said:
I have data in a table as follows:

Company Location Number
Comp A CA 3
Comp A WA 3
Comp B AZ 4
Comp C TX 5
Comp C IL 5
Comp C NY 5

The will always be only one unique "Number" value for each Company,
regardless of how many locations are present. What I am trying to do is
create another column that will average the "Number" field, but I only want
the average to be calculated based off of the one unique Number per Company.
So, for example, in the data above the average would be calculated as (3+4+5)
/ 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in Access, any
help would be appreciated.
Click to expand...
Click to expand...
 
You're welcome. Good luck.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


Jason said:
Thanks to you both, I'll give it a try.


'69 Camaro said:
Hi, Jason.

Number is a Reserved word and should be changed to something else. Avoid
Reserved words and always use alphanumeric characters and the underscore
character only for your names to avoid chasing unnecessary bugs. For
lists
of Reserved words to avoid, please see the following Web pages:

http://support.microsoft.com/default.aspx?id=321266

http://support.microsoft.com/default.aspx?scid=286335
So, for example, in the data above the average would be calculated as
(3+4+5)
/ 3 = 4
Click to expand...

Try:

SELECT AVG([Number]) AS AvgNum
FROM (SELECT [Number]
FROM tblCompanyNums
GROUP BY Company, [Number]);

. . . where tblCompanyNums is the name of the table and [Number] is the
numeric field.
as opposed to (3+3+4+5+5+5) / 5 = 5.
Click to expand...

Actually, that should read (3+3+4+5+5+5) / 6 = 4.167 (or 4 if it's just
one
significant figure).

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.


Jason said:
I have data in a table as follows:

Company Location Number
Comp A CA 3
Comp A WA 3
Comp B AZ 4
Comp C TX 5
Comp C IL 5
Comp C NY 5

The will always be only one unique "Number" value for each Company,
regardless of how many locations are present. What I am trying to do
is
create another column that will average the "Number" field, but I only
want
the average to be calculated based off of the one unique Number per
Company.
So, for example, in the data above the average would be calculated as
(3+4+5)
/ 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in
Access, any
help would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top