473,320 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

help needed

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 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
May 11 '06 #2
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

May 11 '06 #3
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

May 11 '06 #4
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
May 11 '06 #5
works! thanks for help

May 11 '06 #6
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

May 11 '06 #7
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

May 11 '06 #8
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
May 11 '06 #9
"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
May 11 '06 #10
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

May 12 '06 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

28
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...
7
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...
7
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. ...
10
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...
2
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...
2
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...
3
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...
9
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...
2
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...
32
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...
0
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...
0
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...
0
isladogs
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...
1
isladogs
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...
0
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...
1
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)...
0
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...
1
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....
0
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.