Looking for an average time between events

[Jeff G]

I have a “Find duplicates query†that gives me how many times ( Between
[Enter Start Date] And [Enter End Date] ) that a piece of our equipment has
had a particular discrepancy.
Example: 05 Dec 07 Widget Maker Broken Belt
Example: 10 Dec 07 Widget Maker Broken Belt
Example: 06 Jan 08 Widget Maker Broken Belt
Example: 15 Mar 08 Widget Maker Broken Belt
Example: 05 Apr 08 Widget Maker Broken Belt
Example: 15 May 08 Widget Maker Broken Belt



Equipment
Discrepancy # of Occurrences
My query works fine. It displays: Widget maker Broken Belt
6

I have a report that is based on the query, I want to add how many days
average between the occurrences it counts.
Example: 5 day’s between 05 and 10 Dec 07, 27 day’s between 10 Dec 07 and
6 Jan 08, 69 day’s between 6 Jan 08 and 15 Mar 08, 31 day’s between 15 Mar 08
and 5 Apr 08, 40 day’s between 5 Apr and 15 May 08 for an average of; 28.6
days
So my query would look like this:
Equipment Discrepancy # of Occurrences # Day’s
Between Ave
Widget maker Broken Belt 6
28.6

I would like to keep it simple and just stick with # of days.

Any help would be greatly appreciated. I’m running ACCESS 2003

 

[S.Clark]

I would add a new column to the query to calc the #days difference for each
record. Then, on the report, just like the Count(Occurances), add a field
for Avg([Days])

 

[Jeff G]

Sounds like a great way to solve my problem. The problem is, I haven’t
figured out the way to implement it. I’m not program savvy, so I do struggle
allot, and most of what I’ve accomplish with the database has been by
searching this discussion group and “cut, copy, paste†suggestions from other
responses. But mostly ACCESS has done the work for me by using the wizards.
Below is the SQL version of my Query (done by the Find Duplicates wizard)
The last time I had a question like this, the person helping me out asked to
see the SQL version, so what the heck, here it is…..The only thing the wizard
didn’t do (when I originally ran it) was put in the between start date and
end date. I borrowed that from another question.
I’m just a simple minded mechanic…..Programming talk is Greek to me! So
please be gentle.
Just to let you know, my spific question is: How do I add a new column to
the existing query to calculate the # of day’s difference for each record? I
think I can figure out the part for the report…..One step at a time…..

SQL version of Find Duplicates for Spific Cause Query

SELECT [Spific Cause Query].[Asset Affected] AS [Asset Affected Field],
First([Spific Cause Query].[Specific Cause]) AS [Specific Cause Field],
Count([Spific Cause Query].[Asset Affected]) AS NumberOfDups, Sum([Spific
Cause Query].Minutes) AS SumOfMinutes, Last([Spific Cause Query].[Date
Entered]) AS [LastOfDate Entered]
FROM [Spific Cause Query]
WHERE ((([Spific Cause Query].[Date Entered]) Between [Enter Start Date] And
[Enter End Date]))
GROUP BY [Spific Cause Query].[Asset Affected], [Spific Cause Query].[Asset
Affected], [Spific Cause Query].[Specific Cause]
HAVING (((Count([Spific Cause Query].[Asset Affected]))>1) AND
((Last([Spific Cause Query].[Date Entered])) Between [Enter Start Date] And
[Enter End Date]) AND ((Count([Spific Cause Query].[Specific Cause]))>1));

--
Jeff G
Maintenance Tech
Milgard Tempering

I have a “Find duplicates query†that gives me how many times ( Between
[Enter Start Date] And [Enter End Date] ) that a piece of our equipment has
had a particular discrepancy.
Example: 05 Dec 07 Widget Maker Broken Belt
Example: 10 Dec 07 Widget Maker Broken Belt
Example: 06 Jan 08 Widget Maker Broken Belt
Example: 15 Mar 08 Widget Maker Broken Belt
Example: 05 Apr 08 Widget Maker Broken Belt
Example: 15 May 08 Widget Maker Broken Belt



Equipment
Discrepancy # of Occurrences
My query works fine. It displays: Widget maker Broken Belt
6

I have a report that is based on the query, I want to add how many days
average between the occurrences it counts.
Example: 5 day’s between 05 and 10 Dec 07, 27 day’s between 10 Dec 07 and
6 Jan 08, 69 day’s between 6 Jan 08 and 15 Mar 08, 31 day’s between 15 Mar 08
and 5 Apr 08, 40 day’s between 5 Apr and 15 May 08 for an average of; 28.6
days
So my query would look like this:
Equipment Discrepancy # of Occurrences # Day’s
Between Ave
Widget maker Broken Belt 6
28.6

I would like to keep it simple and just stick with # of days.

Any help would be greatly appreciated. I’m running ACCESS 2003