Averages Query

[AC5FF]

I have run a query that will return repair data out of my database and it works without any difficulties. What my query returns is:
Abbr , S/N , TIN Date , REC Date
(FYI S/N = Serial#)
Because I am only looking for one specific abbr, all of this column is the same. The S/N column will be different for each line, or multiple copies depending on the TIN_Date field. (I.E. we repaired S/N XXX five times over the date range of the query).

What I need to show is the average time between fails. So, if S/N "1" was recieved once, there would be a zero or blank returned. But if S/N "3" was repaired four times I would like to know the average time between the individual TIN Dates.

For the life of me I cannot seem to get anything to work right. It is probably something VERY simple that I just am not seeing.

Here is a copy of the base query I am working out of.

SELECT lru.abbr, Work.[s/n], Work.[Rec Date], Work.[TIN Date]
FROM lru INNER JOIN [Work] ON lru.ID = Work.[LRU ID]
WHERE (((lru.abbr)="Engine SCDU") AND ((Work.[TIN Date])>#1/1/2007#))
GROUP BY lru.abbr, Work.[s/n], Work.[Rec Date], Work.[TIN Date]
ORDER BY Work.[s/n], Work.[TIN Date];

 

[Khalid_Afridi]

I think nobody will understand nor will reply, sorry

 

[vbaInet]

Show some sample data of the original query and another of your desired result. It sounds like a Running Average.

 

[AC5FF]

Sorry all. Been under the weather at work and I don't think I was (am) thinking straight. Maybe that's why I can't figure this out...

Okay; sample data run:

Unit   Serial   Receive Date   TurnIN Date
SCDU   1        1 July 07        10 July 07
SCDU   2        19 Mar 08       23 Mar 08
SCDU   2        21 Nov 08       14 Dec 08
SCDU   2        14 Feb 10       23 Feb 10
SCDU   3        8 Apr 09         16 Apr 09
SCDU   3        7 Sept 09       22 Oct 09

What I would like to see is just a list that'll look something like this:

Unit     Times Rec    Serviceability Avg
1                1                ---
2                3               (Avg time between repairs)
3                2               (This would be # days between 16 Apr 09 - 7 Sept 09)

#3 would just be the # days between because it was only received twice. But #2 was received 3 times, so it would be the average. But #1 only being repaired once would have no answer.

Hope this helps!

 

[vbaInet]

So "Rec Serviceability Avg" is the name of one field which is the average?

 

[AC5FF]

In essence yes... That's the end result I want to obtain per serial number.

 

[vbaInet]

Ah, better in code tags. When I first read it it wasn't in code tags

First things first, getting the Times Rec. I will leave you to think about using DCount function. In the meantime your main query should be this:

SELECT DISTINCT Unit FROM Name_Of_Table ORDER BY Unit;

One you've done that query, use the DCount to return the number of occurences of that Unit. You would need an aliased field for that. So the query would now look like:

SELECT DISTINCT Unit, DCount() AS Times_Rec FROM Name_Of_Table ORDER BY Unit;

 

[AC5FF]

Not sure I understand there...

I can easily get the number of times each unit is seen. But in my example of sample data I need to take time between say line 2 TIN date to line 3 REC date. That's where I'm getting hung up.

 

[vbaInet]

You want DISTINCT Units per record correct? Just as your goal table looks like.

To be clear as well, how would the Avg be calculated for Unit 2? From what date to what date?

 

[AC5FF]

I think I understand what you are saying..
Yes, distinct units per record. So if you look at the sample return of my query's data unit #2 was repaired three times. So, for unit #2 I would want to calculate the time from when it was repaired first to the time it was received the 2nd time. Then calculate the time from when it was repaired the 2nd time to the time it was received the third time. Without going in and figuring this exactly, lets just say that was 240 days and 60 days.
So what I am looking to get for serviceability average would be 150 days. (240+60)/2

 

[vbaInet]

Ok, what you're looking to do is almost like vertical calculations but in a right-to-left manner. It can only be done (efficiently) using a function to return the value, hence, the reason why I said you should create a DISTINCT row query.

For further clarity, Unit 2 will be calculated as follows (b'cos I need to know precise dates):

(#days(23 Mar 08 AND  21 Nov 08) + #days(14 Dec 08 AND 14 Feb 10))/2

You will still need the DCount function, by the way

 

[AC5FF]

Yes, I'll still need the Dcount.. No real problem there.
But maybe I'm getting lost on what you mean by a DISTINCT row query... Can you elaborate on that? I would have thought my first query; the one that'll return only the unit I want to look at is a DISTINCT query... Each row is distinct in that all the dates are different, even though the name and serial number are the same...

Or, do I need an intermediate query... Something that would return something like this:

Unit   Serial         IN               OUT         IN           OUT             IN          Out
SCDU   1        1 July 07    10 July 07
SCDU   2        19 Mar 08   23 Mar 08   21 Nov 08  14 Dec 08   14 Feb 10  23 Feb 10
SCDU   3        8  Apr 09    16 Apr 09    7 Sept 09  22 Oct 09

(Grrrr can't get columns to line up right! )
Even if this is the case I'm not really sure how I'd get that data. Maybe a Pivot Table?

 

[vbaInet]

Basically, create a query that will return only Unit and Serial as distinct rows. Don't get confused about the use of the word distinct, I just mean ensure that Serial numbers 2 and 3 are not repeating. You wouldn't need a pivot table. I will write the function to return the AVg now.

 

[vbaInet]

Haven't tested this, but here it is:

Function CalcAvg(unitVal As String, serialVal As Integer) As Variant

    Dim db As dao.Database, rst As dao.Recordset, recDate As Date, turnInDate As Date
    Dim altVal As Boolean, result As Integer, counter As Integer
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT Unit, Serial, Receive_Date, TurnIn__Date FROM table/query " & _
                                "WHERE Unit = '" & unitVal & "' AND Serial = " & serialVal & " " & _
                                "ORDER BY TurnIn_Date", dbOpenSnapshot)
    
    CalcAvg = ""
    altVal = False
    result = 0
    
    With rst
        If .BOF = True Then
            Exit Function
        End If
        
        .MoveFirst
        Do Until .EOF
            If altVal = False Then
                turnInDate = ![[COLOR=Red]TurnIn_Date[/COLOR]]
            Else
                counter = counter + 1
                result = result + DateDiff("d", turnInDate, ![[COLOR=Red]Receive_Date[/COLOR]])
            End If
            
            altVal = IIf(altVal = False, True, False)
        .MoveNext
        Loop
    End With
    
    Set rst = Nothing
    Set db = Nothing
    
    CalcAvg = result / counter

End Function

This is assuming that Serial is a number and the date fields are True date fields.

1. You will need to amend the OpenRecordset bit with the right field names and table/query.
2. Then also, substitute the right field names in the places highlighted in red.

To use the function in the select query given, you write this as a criteria field using the alias "Serviceability Avg":

Serviceability Avg: CalcAvg("[Unit]", "[Serial]")

 

[AC5FF]

Whoa! Way above my head here!! LOL

I take it I will have to get into my VB area and paste that function into a new section? Any particular way to do that so that it is done right?

 

[AC5FF]

Okay... Giving this a few trys ... First, I think I copied the function to an area of the VB code wrong. When I ran the query I got "Unknown Function". I then dug around through the VB areas and found were a bunch of other fuctions were written (ended up in Module1).
And Yes, Im not all that great w/VB yet

Anyway, after pasting this into the module1 area I re-ran the query and got an error "data Type MisMatch in criteria expression."

What I think it might be is not only those two highlighted 'red' areas, but a few others as well. I'm looking at:

    Set rst = db.OpenRecordset("SELECT Unit, Serial, Receive_Date, TurnIn__Date FROM table/query " & _
                                "WHERE Unit = '" & unitVal & "' AND Serial = " & serialVal & " " & _
                                "ORDER BY TurnIn_Date", dbOpenSnapshot)

I think some of this needs changed as well. The SELECT is looking for fields in my query or will it also go to the tables used? Two reasons behind this question...

1st, I edited the query I originally wrote, it now only lists the unit and the serial number; one line each. I took out all turn in or receive date references in the query.

2nd, if you look at the first post here where I listed the original query's SQL; There is no 'Unit', 'Serial', 'Receive_Date', or 'TurnIn_Date' fields. The actual name for the fields are "Abbr", "S/N", REC Date", and "TIN Date". So, I changed these all in the "Set RST" line. It now looks like:

    Set rst = db.OpenRecordset("SELECT abbr, S/N, Re Date, TIN Date FROM table/query " & _
                                "WHERE abbr = '" & unitVal & "' AND S/N = " & serialVal & " " & _
                                "ORDER BY TIN Date", dbOpenSnapshot)

In that same line, where it says "FROM table/query" ... Does this need changed or is this all right the way it is?

I also changed the values after the WHERE and ORDER BY. (Unit, Serial, and TurnIn_Date)
Was that the right thing to do?

Regardless; even after changing all of that and re-running the query I still get the same "Data Type Mismatch" error...
BTW: I also changed the line in my query to this:

Serviceability Avg: CalcAvg("[abbr]","[s/n]")

I know that you said that you just wrote the code and didn't test it... And if I were any better w/VB I may be able to figure this out a little more myself..
Heck - it's a great learning experience for me!!

 

[vbaInet]

Yes you're correct to substitute the fields like I mentioned in point 1. However, you didn't enclose the field names (that have spaces) in square brackets? If you're field names contain spaces (which they really shouldn't AT ALL), then you enclose them in square brackets.

Set rst = db.OpenRecordset("SELECT abbr, [S/N], [Re Date], [TIN Date] FROM [B][COLOR=Red]table/query[/COLOR][/B] " & _
                                "WHERE abbr = '" & unitVal & "' AND [S/N] = " & serialVal & " " & _
                                "ORDER BY [TIN Date]", dbOpenSnapshot)

So you're positive that you put it into a MODULE yes? The table/query should be the name of the table or query where all these fields should be pulled from.

Also is S/N a Text type or a Number? The code assumes that S/N is a Number data type.

There was a mistake in the way the function should be called. This is the right way:

Serviceability Avg: CalcAvg("[abbr]",[s/n])

Also did you change those bits in red to the correct field names?

 

[AC5FF]

I did change the original red items to the correct field names. And I did not think about braketing the field names... spaced that one! And yes, I'm sure it's in the MODULE.

Okay, in your FROM table/query ... My query name is Horton1. That query is using tables 'LRU' and 'Work'. So, if I use FROM Horton1 but that query is only using abbr and s/n fields will it still be able to get the [TIN Date] and [Rec Date] fields from the tables? or should my FROM statement be " FROM lru INNER JOIN [Work] ON lru.ID = Work.[LRU ID] " like it was in my original query?

 

[vbaInet]

Okay, in your FROM table/query ... My query name is Horton1. That query is using tables 'LRU' and 'Work'. So, if I use FROM Horton1 but that query is only using abbr and s/n fields will it still be able to get the [TIN Date] and [Rec Date] fields from the tables? or should my FROM statement be " FROM lru INNER JOIN [Work] ON lru.ID = Work.[LRU ID] " like it was in my original query?

If [Tin Date] and [Rec Date] do not exist in the query (Horton1), then it wouldn't work.

Basically, look at your post #4, create a query that will pull up the records as you have it in your sample data. The exact order, data and number of fields. That's the query you would use for the recordset.

 

[AC5FF]

Here's my code as everything is changed:

Function CalcAvg(unitVal As String, serialVal As Integer) As Variant

    Dim db As dao.Database, rst As dao.Recordset, recDate As Date, turnInDate As Date
    Dim altVal As Boolean, result As Integer, counter As Integer
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("SELECT [abbr], [S/N], [Rec Date], [TIN Date] FROM [horton data] " & _
                                "WHERE abbr = '" & unitVal & "' AND [S/N] = " & serialVal & " " & _
                                "ORDER BY [TIN Date]", dbOpenSnapshot)
    
    CalcAvg = ""
    altVal = False
    result = 0
    
    With rst
        If .BOF = True Then
            Exit Function
        End If
        
        .MoveFirst
        Do Until .EOF
            If altVal = False Then
                turnInDate = ![TIN Date]
            Else
                counter = counter + 1
                result = result + DateDiff("d", turnInDate, ![Rec Date])
            End If
            
            altVal = IIf(altVal = False, True, False)
        .MoveNext
        Loop
    End With
    
    Set rst = Nothing
    Set db = Nothing
    
    CalcAvg = result / counter

End Function

When I run it now I get a compile error-user defined type not defined. The first line, where it defines the function is highlighted in yellow. Then the next line "db As dao.Database" is highlighted blue.

On the positive side, once I clear out the VB error the query just returns "#ERROR" for the Serviceability Avg column.