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? 10 1427
"Lebowski" <bd*******@gmail.com> 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
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 <yournewfieldname>. 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
Fred Zuckerman wrote: "Lebowski" <bd*******@gmail.com> 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
Fred Zuckerman <Zu********@sbcglobal.net> wrote:
: "Lebowski" <bd*******@gmail.com> 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
Thelma Lubkin wrote: Fred Zuckerman <Zu********@sbcglobal.net> wrote: : "Lebowski" <bd*******@gmail.com> 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
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" <th****@alpha2.csd.uwm.edu> wrote in message
news:e4**********@uwm.edu... Fred Zuckerman <Zu********@sbcglobal.net> wrote: : "Lebowski" <bd*******@gmail.com> 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
Fred Zuckerman <Zu********@sbcglobal.net> 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
"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> wrote in message
news:e4**********@uwm.edu... Fred Zuckerman <Zu********@sbcglobal.net> 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 CD********@FortuneJames.com wrote: Thelma Lubkin wrote: Fred Zuckerman <Zu********@sbcglobal.net> wrote: : "Lebowski" <bd*******@gmail.com> 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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: stu_gots |
last post by:
I have been losing sleep over this puzzle, and I'm convinced my train
of thought is heading in the wrong direction. It is difficult to
explain my circumstances, so I will present an identical...
|
by: ChadDiesel |
last post by:
Hello everyone,
I'm having a problem with Access that I need some help with. The short
version is, I want to print a list of parts and part quantities that belong
to a certain part group---One...
|
by: Tina |
last post by:
I have an asp project that has 144 aspx/ascx pages, most with large
code-behind files. Recently my dev box has been straining and taking long
times to reneder the pages in the dev environment. ...
|
by: Mae Lim |
last post by:
Dear all,
I'm new to C# WebServices. I compile the WebService project it return no
errors "Build: 1 succeeded, 0 failed, 0 skipped".
Basically I have 2 WebMethod, when I try to invoke the...
|
by: trihanhcie |
last post by:
I m currently working on a Unix server with a fedora 3 as an os
My current version of mysql is 3.23.58. I'd like to upgrade the version
to 5.0.18.
After downloading from MYSQL.COM the package on...
|
by: Steve K |
last post by:
I got a bit of a problem I like some help on.
I'm designing an online training module for people that work in food
processing plants. This is my target audience. These workers have little
or no...
|
by: Kitana907 |
last post by:
Hi-
I'm attempting to write a module that uses and updates info from two tables and does the following:
Opens the recordset of a table called "tblstoreinv"
If the Needed Field in the...
|
by: smartbei |
last post by:
Hello, I am a newbie with python, though I am having a lot of fun using
it. Here is one of the excersizes I am trying to complete:
the program is supposed to find the coin combination so that with...
|
by: rookiejavadude |
last post by:
I'm have most of my java script done but can not figure out how to add a few buttons. I need to add a delete and add buttong to my existing java program. Not sure were to add it on how. Can anyone...
|
by: =?Utf-8?B?U2l2?= |
last post by:
I have a form that I programmatically generate some check boxes and labels on.
Later on when I want to draw the form with different data I want to clear
the previously created items and then put...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |