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 1454
"Lebowski" <bd*******@gmai l.com> wrote in message
news:11******** **************@ g10g2000cwb.goo glegroups.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 <yournewfieldna me>. 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********@Fort uneJames.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*******@gmai l.com> wrote in message news:11******** **************@ g10g2000cwb.goo glegroups.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********@Fort uneJames.com
Fred Zuckerman <Zu********@sbc global.net> wrote:
: "Lebowski" <bd*******@gmai l.com> wrote in message
: news:11******** **************@ g10g2000cwb.goo glegroups.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********@sbc global.net> wrote: : "Lebowski" <bd*******@gmai l.com> wrote in message : news:11******** **************@ g10g2000cwb.goo glegroups.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********@Fort uneJames.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","Prod uct='" & txtProduct & "'")
SumQQ = Dsum("Quantity * Quality","Table 1","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********@sbc global.net> wrote: : "Lebowski" <bd*******@gmai l.com> wrote in message : news:11******** **************@ g10g2000cwb.goo glegroups.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********@sbc global.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","Prod uct='" & txtProduct & "'")
: SumQQ = Dsum("Quantity * Quality","Table 1","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********@sbc global.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","Prod uct='" & txtProduct & "'") : SumQQ = Dsum("Quantity * Quality","Table 1","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 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 make-believe
challenge in order to avoid confusing the issue further.
Suppose I was hosting a dinner and I wanted to invite exactly 12 guests
from my neighborhood. I'm really picky about that... I have 12 chairs
besides my own, and I want them all...
|
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 list per page. I created a report that groups
the parts by part classification group with a force new page after each
group. The report is based on a query of that week's orders. Some of these
parts have drawings (usually 2 each stored in an...
|
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. After addding another
Crystal report, vs.net will no longer build the project - it just goes
away - no message no nothing. My other dev box will build it but won't run
it in debug.
I ran a vs.net repair but it still does the same thing. vs.net...
|
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 first method it is
working fine. Then when I try to invoke the second method it return me an
error, Just In-Time Debugging, with error message
"An exception 'System.StackOverflowException' has occurred in WebServices"
|
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 the site, I made :
rpm -i MySQL-server-5.0.18-0.i386.rpm
then i have errors that relate to many conflicts. I cannot figure out
why -and- cannot upgrade. Please Help !
| |
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 computer knowledge at all! And they also have outdated, old
browsers, slow modems, old computers, etc. So I need to keep this as
simple as possible and as browser compatible as possible.
The client wants a navigation bar at the bottom of each...
|
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 tblstoreinv table is null and is less than the DCOH field from the tbldcinv then:
The Shipped Field in the tblstoreinv table equals the Needed Field and the DCOH field from tbldcinv = DCOH - Shipped
Else If Needed from tblstoreinv is > DCOH from tbldcinv,...
|
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 10
coins you can reach a certain amoung, taken as a parameter. Here is the
current program:
coins = (100,10,5,1,0.5)
anslist =
def bar(fin, hist = {100:0,10:0,5:0,1:0,0.5:0}):
s = sum(x*hist for x in hist)
|
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 help? my script is below. thank you
import java.awt.*; //import all java.awt
import java.awt.event.*; //import all java.awt.event
import java.util.*; //import all java.util
import javax.swing.*; //import all javax.swing
class Product...
|
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 some new ones on.
In my code I am doing the following:
For Each ctrl In tpMain.Controls
If TypeOf (ctrl) Is CheckBox Then
If ctrl.Name.StartsWith("chkS") Then
ctrl.Visible = False
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |