By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 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
Share this Question
Share on Google+
10 Replies


P: n/a
"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

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 <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

P: n/a
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

P: n/a
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

P: n/a
works! thanks for help

May 11 '06 #6

P: n/a
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

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" <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

P: n/a
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

P: n/a
"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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.