Average Days between several dates in a query

[Jeff G]

I have a “Find Duplicate Query†that is using another Query for its data
(basically taking many fields from a table and selecting the few that I need
for this query). I’m looking specifically for the average number of days
between all the duplicates of machinery malfunctions for that specific cause
and Asset. My main data table records information such as Asset type (Auto
Seamer) What was broken (will not move(this is not listed in this particular
query, but it is in others)) and the Specific Cause of the malfunction
(bearing failure (roll)) plus the date (medium date format). My current “Find
Duplicates Query lists everything (all fields are “grouped by†in the Total).
So it looks like this:

Test 1
Asset Affected Specific Cause Date Entered

Auto Seamer Bearing Failure (Roll) 09-Oct-07
Auto Seamer Bearing Failure (Roll) 04-Dec-07
Auto Seamer Bearing Failure (Roll) 06-Dec-07
Auto Seamer Bearing Failure (Roll) 14-Dec-07
Auto Seamer Bearing Failure (Roll) 28-Apr-08
Auto Seamer Bearing Failure (Roll) 05-Jun-08
Auto Seamer Diamond Belt Fraying 14-Aug-07
Auto Seamer Diamond Belt Fraying 20-Sep-07
Auto Seamer Diamond Belt Fraying 05-Oct-07
Auto Seamer Diamond Belt Fraying 23-Oct-07
Auto Seamer Diamond Belt Fraying 07-Nov-07
Auto Seamer Diamond Belt Fraying 12-Nov-07
Auto Seamer Diamond Belt Fraying 13-Nov-07
Auto Seamer Diamond Belt Fraying 16-Nov-07
Auto Seamer Diamond Belt Fraying 05-May-08
Auto Seamer Diamond Belt Off 31-Oct-07
Auto Seamer Diamond Belt Off 07-Nov-07
Auto Seamer Diamond Belt Off 12-Nov-07
Auto Seamer Diamond Belt Off 26-Nov-07
Auto Seamer Diamond Belt Off 06-Dec-07
Auto Seamer Diamond Belt Off 31-Mar-08


What I want to do is calculate the average number of days for each group of
Specific Causes. So Instead of just showing 6 different dates forAuto Seamer,
Bearing Failure (roll)), you should see:

Test 1
Asset Affected Specific Cause Average # Days

Auto Seamer Bearing Failure (Roll) 45*
Auto Seamer Diamond Belt Fraying) 27*
Auto Seamer Diamond Belt Off 16*

* these numbers are just representative, not actual averages

If I change the Total to show Avg instead of grouped by, this is what I get:

Test 1
Asset Affected Specific Cause AvgOfDate Entered

Auto Seamer Bearing Failure (Roll) 39467.6666666667
Auto Seamer Diamond Belt Fraying 39395.1111111111
Auto Seamer Diamond Belt Off 39450.8571428571

The result in the AvgOfDate Entered field appears to be in Serial Date
format (I think). If so is this an average of all the entries and, how do I
convert the serial date to display the number of days?

Foot Note: The first and third tables are exactly what my query looks like
in Datasheet View, just shorter.

Any help at all would be greatly appreciated, but please be genital. I’m
fairly new to ACCESS. Most of what I’ve done has been thru trial and error,
cut, copy, paste from the discussion site and by using the included wizards
in this version of ACCESS (2003).

 

[KARL DEWEY]

Use these two queries. The first organizes the data in sequence so as to
subtract the dates form each other in the second query. Use the second in a
third to average the malfunction days.
Test_1_Seq ---
SELECT Q.[Asset Affected], Q.[Specific Cause], Q.[Date Entered], (SELECT
COUNT(*) FROM Test_1 Q1
WHERE Q1.[Asset Affected] = Q.[Asset Affected]
AND Q1.[Specific Cause] = Q.[Specific Cause]
AND Q1.[Date Entered] < Q.[Date Entered])+1 AS Seq
FROM Test_1 AS Q
ORDER BY Q.[Asset Affected], Q.[Specific Cause], Q.[Date Entered];


SELECT Test_1_Seq.[Asset Affected], Test_1_Seq.[Specific Cause],
[Test_1_Seq_1].[Date Entered]-[Test_1_Seq].[Date Entered] AS Days
FROM Test_1_Seq INNER JOIN Test_1_Seq AS Test_1_Seq_1 ON
(Test_1_Seq.[Specific Cause] = Test_1_Seq_1.[Specific Cause]) AND
(Test_1_Seq.[Asset Affected] = Test_1_Seq_1.[Asset Affected])
WHERE (((Test_1_Seq_1.Seq)=[Test_1_Seq].[Seq]+1));

 

[Jeff G]

Thank You Mr Dewey!!! It's working great! It took me a bit to get it going,
but the trouble was on my end (it helps when you spell specific correctly!)
The only thing I would like to change is the average result. Some of the
number of days comes out to be rather large decimal placed numbers
(36.0265987 as an example) I would like to to just show a hole number. So far
I havent been able to figure that one out. Any suggestions?