Need to average data in a query

[Chad]

Hi All,

I have 2 tables for a drilling database. the first table has the hole
number (plus other stuff). the second table has the hole details. My query
gives me the hole number from the first table and the hole detail from the
second. My problem is that I have a detail for every cm of the drilled
hole. With holes ranging from 15m to 20m, that is way too much data. Is
there a way to averagethe readings 1-100, 101-200, 201-300, etc. The other
catch is, my detail for each hole is text (soft, meduim, hard) So I am
assuming I need to assign a number to each (1-soft, 2-medium, 3-hard) add
those numbers up for 1-100 and then average it, and if per say it ends up a
2 then output medium for the section.

Is this possible? I have no experience with access, but have done some
programming with VBA & Excel

Thanks
Chad

 

[eeee]

Hi All,

I have 2 tables for a drilling database. the first table has the hole
number (plus other stuff). the second table has the hole details. My
query gives me the hole number from the first table and the hole detail
from the second. My problem is that I have a detail for every cm of the
drilled hole. With holes ranging from 15m to 20m, that is way too much
data. Is there a way to averagethe readings 1-100, 101-200, 201-300,
etc. The other catch is, my detail for each hole is text (soft, meduim,
hard) So I am assuming I need to assign a number to each (1-soft,
2-medium, 3-hard) add those numbers up for 1-100 and then average it, and
if per say it ends up a 2 then output medium for the section.

Is this possible? I have no experience with access, but have done some
programming with VBA & Excel

Thanks
Chad

 

[Ken Sheridan]

Chad:

Firstly create another table DepthRanges say, with columns DepthFrom,
DepthTo so the table would look like this:

DepthFrom DepthTo
1 100
101 200
201 300
<and so on to>
2401 2500

Allow enough margin to cater for the maximum depth you'd anticipate.

Rather than adding a column to your details table for a numeric
representation of the detail, create another table, DetailValues say, with
columns Detail and DetailValue, so this table would look like this:

Detail DetailValue
Soft 1
Medium 2
Hard 3

You can now join the tables in a query along the following lines to get the
averages:

SELECT [Holes].[Hole Number], [DepthFrom], [DepthTo],
AVG([DetailValue]) AS [AverageDetail]
FROM [DepthRanges], [Holes], [Details], [DetailValues]
WHERE [Details].[Depth] >= [DepthRanges].[DepthFrom]
AND [Details].[Depth] <= [DepthRanges].[DepthTo]
AND [Holes].[HoleNumber] = [Details].[HoleNumber]
AND [Details].[Detail] = [DetailValues].[Detail]
GROUP BY [Holes].[Hole Number], [DepthFrom], [DepthTo];

This will give you the average numeric value of the details per metre per
hole, which will generally not be an integer of course, so there will be no
direct correspondence between this and the text detail. You can round this to
give an integer, but bear in mind that Access uses 'banker's rounding'
(round-half-even), so for values of 1.5 and 2.5, both round to 2, i.e. Medium
in your case. Is that what you'd want? If so you could join the above query
to the DetailValues table to give you the corresponding detail text value,
e.g.

SELECT [DepthFrom], [DepthTo], [Hole Number], [Detail]
FROM [TheAboveQuery], [DetailValues]
WHERE ROUND([TheAboveQuery].[AverageDetail]) = [DetailValues].[DetailValue];

Finally, are you sure that a simple averaging gives a statistically valid
result for each metre section? I'm not saying it isn't as I'm not qualified
to judge, merely posing the question.

Ken Sheridan
Stafford, England

 

[John Spencer]

Perhaps a query like the following would work for you.

SELECT Holes.HoleNumber
, 1 + (((HoleDetails.Depth -1) \100) * 100) as StartPoint
, Avg(IIF(HoleDetails.Reading="Soft",1
,IIF(HoleDetails.Reading="Medium",2
,IIF(HoleDetails.Reading="Hard",3,Null)))) as AvgReading
FROM Holes INNER JOIN HoleDetails
ON Holes.HoleNumber = HoleDetails.HoleNumber
GROUP BY Holes.HoleNumber
,1 + (((HoleDetails.Depth -1) \100) * 100)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County