 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2006Average
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  SignatureGood Luck BS"D > I have data in a table as follows: > [quoted text clipped - 13 lines] > / 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in Access, any > help would be appreciated. Sorry For the third part I should have used the AVG SELECT Avg(QueryName.Number) AS AvgOfNumbe FROM QueryName SignatureGood Luck BS"D > 1. Create a Group by query that includes ony the two relevent fields > [quoted text clipped - 30 lines] > > / 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in Access, any > > help would be appreciated. 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 SignatureGood Luck BS"D > 1. Create a Group by query that includes ony the two relevent fields > [quoted text clipped - 30 lines] > > / 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in Access, any > > help would be appreciated. > 2. You can use code > DSum("[Number]","[QueryName]") / DCount("*","[QueryName]") 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. > 1. Create a Group by query that includes ony the two relevent fields > [quoted text clipped - 35 lines] >> any >> help would be appreciated. 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. SignatureGood Luck BS"D > > 2. You can use code > > DSum("[Number]","[QueryName]") / DCount("*","[QueryName]") [quoted text clipped - 50 lines] > >> any > >> help would be appreciated. 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. >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 [quoted text clipped - 58 lines] >> >> any >> >> help would be appreciated. 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 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. 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. > I have data in a table as follows: > [quoted text clipped - 13 lines] > / 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in Access, any > help would be appreciated. Thanks to you both, I'll give it a try. > Hi, Jason. > [quoted text clipped - 49 lines] > > / 3 = 4, as opposed to (3+3+4+5+5+5) / 5 = 5. I'm a beginner in Access, any > > help would be appreciated. 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. > Thanks to you both, I'll give it a try. > [quoted text clipped - 60 lines] >> > Access, any >> > help would be appreciated. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.