434,808 Members | 1,481 Online
Need help? Post your question and get tips & solutions from a community of 434,808 IT Pros & Developers. It's quick & easy.

# help needed

 P: n/a Problem i have records like these: name quantity quality A 5 2.5 B 4 1 B 3 2 C 1 1.5 .... ... ... I want to create a new table with averague quality value for each records that have the same "name", in this example: name quality A 2.5 B 1.428 C 1.5 ... ... anybody can help? May 11 '06 #1
10 Replies

 P: n/a "Lebowski" wrote in message news:11**********************@g10g2000cwb.googlegr oups.com... Problem i have records like these: name quantity quality A 5 2.5 B 4 1 B 3 2 C 1 1.5 ... ... ... I want to create a new table with averague quality value for each records that have the same "name", in this example: name quality A 2.5 B 1.428 C 1.5 ... ... anybody can help? Create 2 queries. The first calculates the sums by product. The second calulates the average. My table is named Table1 with fields named Product, Quantity, and Quality. 1st Query = qryProduct1: ------------------------------ SELECT Product, Sum([Quantity]*[Quality]) AS SumOfQQ, Sum(Quantity) AS SumOfQuantity FROM Table1 GROUP BY Product; 2nd Query = qryProduct2: ------------------------------------ SELECT Product, [SumOfQQ]/[SumOfQuantity] AS AvgQuality FROM qryProduct1; Fred Zuckerman May 11 '06 #2

 P: n/a Lebowski wrote: Problem i have records like these: name quantity quality A 5 2.5 B 4 1 B 3 2 C 1 1.5 ... ... ... I want to create a new table with averague quality value for each records that have the same "name", in this example: name quality A 2.5 B 1.428 C 1.5 ... ... anybody can help? I try not to hog the easy questions so that others can improve but I'll make an exception for this one. First change the name of the field called 'name' to a name that is not a reserved name in Access. Then use the Avg function with GROUP BY . You should use 'Avg(Quality) AS AvgQuality' since using the name Quality again will cause a circular reference. The SQL of your result should look like: warning: spoiler ahead .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. SELECT theName, Avg(Quality) AS AvgQuality FROM tblNames GROUP BY theName; James A. Fortune CD********@FortuneJames.com MichTel Communications, LLC was selected by Oakland County in 2005 to provide all of Oakland County residents with free wireless Internet service by the end of 2007. MichTel will build out the needed wireless infrastructure throughout the county's 910 square miles to deliver Internet and voice over Internet protocol (VoIP) phone services. Service will start in pilot areas then expand to the rest of the county by the end of 2007. -- http://www.oakgov.com/wireless/news/partners.html May 11 '06 #3

 P: n/a Fred Zuckerman wrote: "Lebowski" wrote in message news:11**********************@g10g2000cwb.googlegr oups.com... Problem i have records like these: name quantity quality A 5 2.5 B 4 1 B 3 2 C 1 1.5 ... ... ... I want to create a new table with averague quality value for each records that have the same "name", in this example: name quality A 2.5 B 1.428 C 1.5 ... ... anybody can help? Create 2 queries. The first calculates the sums by product. The second calulates the average. My table is named Table1 with fields named Product, Quantity, and Quality. 1st Query = qryProduct1: ------------------------------ SELECT Product, Sum([Quantity]*[Quality]) AS SumOfQQ, Sum(Quantity) AS SumOfQuantity FROM Table1 GROUP BY Product; 2nd Query = qryProduct2: ------------------------------------ SELECT Product, [SumOfQQ]/[SumOfQuantity] AS AvgQuality FROM qryProduct1; Fred Zuckerman Fred, Your solution makes more sense than mine. The existence of the quantity field should have tipped me off that some kind of weighted average was desired. I took 'for each records' too literally. James A. Fortune CD********@FortuneJames.com May 11 '06 #4

 P: n/a Fred Zuckerman wrote: : "Lebowski" wrote in message : news:11**********************@g10g2000cwb.googlegr oups.com... : 1st Query = qryProduct1: : ------------------------------ : SELECT Product, Sum([Quantity]*[Quality]) AS SumOfQQ, Sum(Quantity) AS : SumOfQuantity FROM Table1 GROUP BY Product; : 2nd Query = qryProduct2: : ------------------------------------ : SELECT Product, [SumOfQQ]/[SumOfQuantity] AS AvgQuality FROM qryProduct1; How would you do this completely in SQL, without any reference to queries? --thelma : Fred Zuckerman May 11 '06 #5

 P: n/a works! thanks for help May 11 '06 #6

 P: n/a Thelma Lubkin wrote: Fred Zuckerman wrote: : "Lebowski" wrote in message : news:11**********************@g10g2000cwb.googlegr oups.com... : 1st Query = qryProduct1: : ------------------------------ : SELECT Product, Sum([Quantity]*[Quality]) AS SumOfQQ, Sum(Quantity) AS : SumOfQuantity FROM Table1 GROUP BY Product; : 2nd Query = qryProduct2: : ------------------------------------ : SELECT Product, [SumOfQQ]/[SumOfQuantity] AS AvgQuality FROM qryProduct1; How would you do this completely in SQL, without any reference to queries? --thelma : Fred Zuckerman Maybe something like: SELECT theName, Sum(Quality * Quantity) / Sum(Quantity) AS AvgQuality FROM tblNames WHERE Quantity IS NOT NULL GROUP BY theName; Perhaps the extra query is used to allow checking for the case of a zero denominator. You don't want that to happen. James A. Fortune CD********@FortuneJames.com May 11 '06 #7

 P: n/a Thelma, The two queries are regular A2K queries. I put them in SQL language so you could copy and paste them into A2K if you didn't understand them. It's also easier to give the SQL statement than trying to describe how to fill in the "grid" of a query in design view. I'm not sure what you mean by "without any reference to queries" ? I suppose that if you wanted to create function you could try (aircode): Public Function AvgQuality (txtProduct as String) as Double Dim SumQ as Double Dim SumQQ as Double SumQ = Dsum("Quantity","Table1","Product='" & txtProduct & "'") SumQQ = Dsum("Quantity * Quality","Table1","Product='" & txtProduct & "'") AvgQuality = SumQQ / SumQ End Function Fred Zuckerman Fred "Thelma Lubkin" wrote in message news:e4**********@uwm.edu... Fred Zuckerman wrote: : "Lebowski" wrote in message : news:11**********************@g10g2000cwb.googlegr oups.com... : 1st Query = qryProduct1: : ------------------------------ : SELECT Product, Sum([Quantity]*[Quality]) AS SumOfQQ, Sum(Quantity) AS : SumOfQuantity FROM Table1 GROUP BY Product; : 2nd Query = qryProduct2: : ------------------------------------ : SELECT Product, [SumOfQQ]/[SumOfQuantity] AS AvgQuality FROM qryProduct1; How would you do this completely in SQL, without any reference to queries? --thelma : Fred Zuckerman May 11 '06 #8

 P: n/a Fred Zuckerman wrote: : Thelma, : The two queries are regular A2K queries. I put them in SQL language so you : could copy and paste them into A2K if you didn't understand them. It's also : easier to give the SQL statement than trying to describe how to fill in the : "grid" of a query in design view. I prefer to use SQL language directly w/o ever having to create a query. I'm much more comfortable with code than with things like queries and wizards. I'd like to hear that something like James Fortune's suggestion, SELECT theName, Sum(Quality * Quantity) / Sum(Quantity) AS AvgQuality FROM tblNames WHERE Quantity IS NOT NULL GROUP BY theName; will work, perhaps using Quantity > 0 in the WHERE clause. : I'm not sure what you mean by "without any reference to queries" ? I suppose : that if you wanted to create function you could try (aircode): : Public Function AvgQuality (txtProduct as String) as Double : Dim SumQ as Double : Dim SumQQ as Double : SumQ = Dsum("Quantity","Table1","Product='" & txtProduct & "'") : SumQQ = Dsum("Quantity * Quality","Table1","Product='" & txtProduct & : "'") : AvgQuality = SumQQ / SumQ : End Function The function is nicely straightforward--but probably less efficient than the SELECT? --thelma : Fred Zuckerman May 11 '06 #9

 P: n/a "Thelma Lubkin" wrote in message news:e4**********@uwm.edu... Fred Zuckerman wrote: : Thelma, : The two queries are regular A2K queries. I put them in SQL language so you : could copy and paste them into A2K if you didn't understand them. It's also : easier to give the SQL statement than trying to describe how to fill in the : "grid" of a query in design view. I prefer to use SQL language directly w/o ever having to create a query. I'm much more comfortable with code than with things like queries and wizards. I'd like to hear that something like James Fortune's suggestion, SELECT theName, Sum(Quality * Quantity) / Sum(Quantity) AS AvgQuality FROM tblNames WHERE Quantity IS NOT NULL GROUP BY theName; will work, perhaps using Quantity > 0 in the WHERE clause. : I'm not sure what you mean by "without any reference to queries" ? I suppose : that if you wanted to create function you could try (aircode): : Public Function AvgQuality (txtProduct as String) as Double : Dim SumQ as Double : Dim SumQQ as Double : SumQ = Dsum("Quantity","Table1","Product='" & txtProduct & "'") : SumQQ = Dsum("Quantity * Quality","Table1","Product='" & txtProduct & : "'") : AvgQuality = SumQQ / SumQ : End Function The function is nicely straightforward--but probably less efficient than the SELECT? --thelma : Fred Zuckerman Yes, I imagine it would be significantly slower. Fred May 11 '06 #10

 P: n/a CD********@FortuneJames.com wrote: Thelma Lubkin wrote: Fred Zuckerman wrote: : "Lebowski" wrote in message : news:11**********************@g10g2000cwb.googlegr oups.com... : 1st Query = qryProduct1: : ------------------------------ : SELECT Product, Sum([Quantity]*[Quality]) AS SumOfQQ, Sum(Quantity) AS : SumOfQuantity FROM Table1 GROUP BY Product; : 2nd Query = qryProduct2: : ------------------------------------ : SELECT Product, [SumOfQQ]/[SumOfQuantity] AS AvgQuality FROM qryProduct1; How would you do this completely in SQL, without any reference to queries? --thelma : Fred Zuckerman Maybe something like: SELECT theName, Sum(Quality * Quantity) / Sum(Quantity) AS AvgQuality FROM tblNames WHERE Quantity IS NOT NULL GROUP BY theName; Perhaps the extra query is used to allow checking for the case of a zero denominator. You don't want that to happen. James A. Fortune CD********@FortuneJames.com How about: SELECT theName, IIf(Sum(Quantity) <> 0, Sum(Quality * Quantity) / Sum(Quantity), 'Div by 0 problem') AS AvgQuality FROM tblNames WHERE Quantity IS NOT NULL GROUP BY theName; James A. Fortune CD********@FortuneJames.com and we think SOAP is a really great way of building both infrastructure and application protocols. SOAP enables rich extensibility & security. Metadata or SOAP. Under Windows Communications Foundation (WCF), you can code C# directly into XSLT. -- Doug Purdy, PDC 05, COM326: Web Services for XML Programmers May 12 '06 #11

### This discussion thread is closed

Replies have been disabled for this discussion.