-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You mean you have something like this:
Vendor, Plant Type, Plant Size, Cost
ABC Nursery Sequoia Sempervirgens 12" $12.00
ABC Nursery Sequoia Sempervirgens 12" $12.00
ABC Nursery Sequoia Sempervirgens 12" $12.00
???
If so, I strangle the DB designer 'cuz s/he didn't put any data
integrity constraints on the table. You'd have to get rid of all the
duplicates & then create data integrity constraints to avoid this
problem in the future.
But, if you have:
Vendor, Plant Type, Plant Size, Cost
ABC Nursery Sequoia Sempervirgens 12" $12.00
XYZ Nursery Sequoia Sempervirgens 12" $12.50
Holly Nursery Sequoia Sempervirgens 12" $12.25
And you want to get the avg cost of the same plant types, w/ the same
size, you do this:
SELECT [Plant Type], [Plant Size], Avg(Cost) As AvgCost
FROM table_name
GROUP BY [Plant Type], [Plant Size]
Whic will yield (on just the Sequoia):
Plant Type Size Cost
Sequoia Sempervirgens 12" $12.25 <- the mean average
If you want the median value you'll have to use a function that calcs
that. There are various examples on the web. Google for "median
average" sql. That should get some examples. I know there's one out
there that was done for SQL Server's T-SQL language.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRAUjH4echKqOuFEgEQILzgCeN01YeswrjW6ULEa7n2RhdtRPl1gAnRP+
1Zm2iQKV0Kuv+Bpth6lNSqWv
=aFH8
-----END PGP SIGNATURE-----
Pre_Live_Wire said:
ok let me explain the whole thing
I work for a garden center and years a go we had an access program created
for us, and the person has since left the area. We use this for pricing our
inventoory.
We enter all our data in line by line. We buy our plants from different
vendors, which gives us the scenario of having different costs. This program
creates reports based on ourcost. our input columns are as follows:
Vendor, Plant Type, Plant Size, Cost We have a report that allows us to
automatically take the cost of the plant, multiply by 2 (for easy math) and
then prints out the retail.
Off the 800+ items we enter, there are many duplicate plants and plant
sizes. I would like to create the average price of the items if the plant
type and plant size match. Right now if I have Plant A @ 12" listed 3 times
in the system, it will calculate and print out the 3 items separetly. I want
to make it so that plant A is only listed once and calculates the average
price.
Thanks for the help. Hope this better explains it.
Live Wire.
:
Pre_Live_Wire wrote:
I have a Query that uses one field as the main catagory (Plant Variety). I
would like to create a field that when the plant name matches (field 1), then
the costs (field 2) would be averaged and displayed in field 3 for all of
matching field 1 items. This would then allow a report to be made with this
new field.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Not completely sure what you mean but, have you tried the AVG()
aggregate function:
SELECT [plant variety], AVG([costs]) As AvgCosts
FROM table_name
WHERE ...
GROUP BY [plant variety]
AVG() returns the average of the column "costs" in the recordset
population only. IOW, the average will be on only the records filtered
by the WHERE clause.
The [costs] column can't be included in the SELECT clause 'cuz the GROUP
BY would have to group on it and the AVG would then be for plant variety
AND costs instead of just for the plant variety. Read the SQL Reference
articles in the Access Help for more info on the AVG() function and
GROUP BY.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRAStcYechKqOuFEgEQK2qACg9QrMjNpWO77glSKG2tzJ6tkWYeYAoPu1
UTtnu1HIDxpOvh0XKRG5cCCj
=vd+H
-----END PGP SIGNATURE-----
