Max, min and average of a recordset 2

[JaneInMA]

Is there an easy way to get the minimum, maximum and average value of a recordset.
I currently have a recordset that works on an union query and that is filtered for dates and selection query. I then take two values put them in a function to create a number. I wish to return the average, the minimum and maximum, and the number of records.
I am doing this but very clumsily. Comments on teh code and my methods would be appreciated.

Private Function IssuedReceived()
Dim strsite As String
Dim db As Database, rs As Recordset, varTestProgram As Variant, lngPrevOrderID
Dim booStatus As Boolean, intFlagCount As Integer
Dim intAge As Integer
Dim intCountAge As Integer

Dim intCountRecords As Integer
Dim intmax As Integer
Dim intmin As Integer
Dim DtStart As Date
Dim DtEnd As Date
Dim strSQL As String
intmin = 0
intmax = 0
intAge = 0
DtStart = Me.BeginDate
DtEnd = Me.EndDate
If Me.Site <> &quot;Both Sites&quot; Then
If Me.Site = &quot;Allston&quot; Then
strsite = &quot;A&quot;
ElseIf Me.Site = &quot;Framingham&quot; Then
strsite = &quot;F&quot;
End If
strSQL = &quot;SELECT DateIssued, Origin, DCRReceivedDate&quot; _
& &quot; FROM qryUnionMOVA WHERE qryUnionMOVA.Origin = '&quot; & strsite & &quot;' AND &quot; & &quot; qryUnionMOVA.DateIssued Between #&quot; & DtStart & &quot;# And #&quot; & DtEnd & &quot;#&quot;

ElseIf Me.Site = &quot;Both Sites&quot; Then
strSQL = &quot;SELECT DateIssued, Origin, DCRReceivedDate&quot; _
& &quot; FROM qryUnionMOVA WHERE qryUnionMOVA.DateIssued Between #&quot; & DtStart & &quot;# And #&quot; & DtEnd & &quot;#&quot;
End If


Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.MoveFirst
intAge = OpenWorkDays(rs!DCRReceivedDate, rs!DateIssued)
intmin = intAge

rs.MoveFirst
Do Until rs.EOF
intAge = OpenWorkDays(rs!DCRReceivedDate, rs!DateIssued)
'intMin = intAge
intCountAge = intCountAge + intAge
Select Case intAge
Case Is > intmax
intmax = intAge
Case Is < intmin
intmin = intAge

End Select
rs.MoveNext
Loop
intCountRecords = rs.RecordCount
Me.countissued = intCountRecords
Me.averageissued = intCountAge / intCountRecords
Me.Minissued = intmin
Me.maxissued = intmax
End Function

 

[jebry]

Hi!

I am assuming OpenWorkDays is a procedure you wrote. If that is the case, then you could make it a public function. Then you could call it directly from your SQL and you could use aggregate functions, Min Max Ave, on the results:

Min(OpenWorkDays([DCRReceivedDate],[DateIssued])) as MinAge etc.

hth
Jeff Bridgham

 

[Databaseguy]

DMin, DMax, DAvg.... Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[JaneInMA]

OpenWorkdays is actually a function changed slightly from the VBA Developers handbook by Getz and Gilbert and it is a public function. The funny thing is that Access can not handle sorting by the result in any manner at all. If I even create a simple query and use this function then sort on the expression it refuses to sort logically!
as for using Dmin (worth trying )- would I use the recordset as the expression. I am just confused as to what I would use as the selection criteria.
Dmin(OpenWorkDays([DCRReceivedDate],[DateIssued]), qryUnionMOVA, strSQL) ????
FYI this is openworkdays
Function OpenWorkDays(OpenDate, Optional CloseDate)

Dim OpDate As Date
Dim ClDate As Date
Dim i As Date 'counter
Dim WrkDays As Integer

If Not IsDate(OpenDate) Then
MsgBox &quot;Valid opendate not supplied&quot;
Exit Function
Else
OpDate = CVDate(OpenDate)
End If

If (IsMissing(CloseDate) Or Not IsDate(CloseDate)) Then CloseDate = Date

ClDate = CVDate(CloseDate)
WrkDays = 0 'initialize for working days

For i = OpDate To ClDate
If WeekDay(i) <> 1 And WeekDay(i) <> 7 Then
WrkDays = WrkDays + 1


End If
Next

OpenWorkDays = WrkDays


End Function

 

[jebry]

Hi!

I am a little confused. I don't see in your original post where you are doing any sorting. If you could elaborate, I would appreciate it.

Jeff Bridgham

 

[JaneInMA]

I am just saying that if I use openworkdays in a query access can not cope with finding the maximum, or minimum. I did set up the aggregate functions and then looked at the min and max and especially looking at the max function the wrong value was returned. I then broke this down by looking at the set of values returned and asking Access to sort by openworkdays and saw access jumbled the values. (kind of manually breaking down the max function.
If you can explain why the program can not deal with the values returned by Openworkdays I would appreciate it.

 

[jebry]

Hi!

I copied the function into one of my databases and the queries I built around it, using the aggregate functions or using sorting worked well. Maybe you can show me the syntax you used because I am no more confused than ever!

Jeff Bridgham

 

[JaneInMA]

SELECT tblDCRMastered.DCR_txt_SOPNumber, tblDCRMastered.DCR_dtt_DateIssued, tblDCRMastered.DCRTypedDate, OpenWorkDays([tblDCRMastered]![DCR_dtt_DateIssued],[tblDCRMastered]![DCRTypedDate]) AS workdays, DateDiff(&quot;d&quot;,[DCR_dtt_DateIssued],[DCRTypedDate]) AS actual
FROM tblDCRMastered
WHERE (((tblDCRMastered.DCRTypedDate) Between #8/14/2001# And #8/29/2001#));
this query just doesnt sort correctly. Can you see the problem?

 

[jebry]

Hi!

No, I cannot see why that wouldn't sort properly, at least after you put in the Order By clause. Why don't you try this query and see if it gives you the numbers you are looking for:

Select Max(OpenWorkDays([tblDCRMastered]![DCR_dtt_DateIssued],[tblDCRMastered]![DCRTypedDate])) as MaxAge, Min(OpenWorkDays([tblDCRMastered]![DCR_dtt_DateIssued],[tblDCRMastered]![DCRTypedDate])) as MinAge, Avg(OpenWorkDays([tblDCRMastered]![DCR_dtt_DateIssued],[tblDCRMastered]![DCRTypedDate])) as AvgAge, Count(OpenWorkDays([tblDCRMastered]![DCR_dtt_DateIssued],[tblDCRMastered]![DCRTypedDate]) as TotalCount
From tblDCRMastered Where tblDCRMastered.DCRTypedDate) Between #8/14/2001# And #8/29/2001#

This is basically the query that worked for me!

hth
Jeff Bridgham

 

[JaneInMA]

Ok, debugging like crazy, when I run the query (I take it the placement of the last ) was a mistake and it should have been earlier. My db will not run teh query at all.
It gives me this error
The Microsoft Jet database engine could not execute the SQL statement because it contains a field that has an invalid data type. (Error 3169)

But if I break it down into each aggregate function it will run! What the heck is my poor db doing? Should I be checking references or something?ps my version of query is
SELECT Max(OpenWorkDays([tblDCRMastered]![DCR_dtt_DateIssued],[tblDCRMastered]![DCRTypedDate])) AS MaxAge, Min(OpenWorkDays([tblDCRMastered]![DCR_dtt_DateIssued],[tblDCRMastered]![DCRTypedDate])) AS MinAge, Avg(OpenWorkDays([tblDCRMastered]![DCR_dtt_DateIssued],[tblDCRMastered]![DCRTypedDate])) AS AvgAge, Count(OpenWorkDays([tblDCRMastered]![DCR_dtt_DateIssued],[tblDCRMastered]![DCRTypedDate])) AS TotalCount
FROM tblDCRMastered
WHERE (((tblDCRMastered.DCRTypedDate) Between #8/14/2001# And #8/29/2001#));

 

[jebry]

Hi!

First, you were absolutely right about the final parentheses, I don't know how it got all the way down there. In the final aggregate function try this:

Count([tblDCRMastered]![DCRTypedDate]) as TotalCount

That might work better.

hth
Jeff Bridgham

 

[MichaelRed]

Well, I'm thoroughly lost. You DO NOT have an &quot;Order By&quot; clause to do any &quot;SORTING&quot;, so I know of NO reason to expect any results to be ordered or sorted. This, alone, is only cause for concern, NOT panic. But NOW you are discussing the Sorting (actually ORDERING) of an aggregate query which has NO Group By Clause either, so INMCO (&quot;In My COnfused opinion) you should only get the SINGLE record anyway, so ordering (Sorting if you MUST) is not applicable.

So, you stalwarts of the purity and clarity of SQL, please enlighten me?

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[JaneInMA]

Michael, Sorry to have confused.
Crux of the problem, I tried to create union queries with aggregate fnctions- min max and avg. I then checked the values these functions were returning by creating a query showing each individual value then sorting it to allow myself to see what the max/min value was.I was experiencing a bug that when I did that the table did not seem to be sorting in an explicable manner.
As to the SQL I never claim my sql is pure or clear but try to improve it as I go.
I have to show documentation to show my queries do what I think they do before I can implement the query hence the check.
Why it was acting buggy I am unsure.
However Jeff is being great here working me through some of the problems.

 

[MichaelRed]

Hmmmmmmmmmmmm,

You started the thread discussing qryUnionMOVA, which at least has the naming convention of a union query. In latter posts you seem to be attempting to select the aggregate functions from tblDCRMastered. It appears -to me- that the switch between the two was 'lost', as I see no mention of the change -or any relation between the two. I was going to attempt to point out some issues in your original code approach, and then attempted to follow the logic of the SQL w/ domain aggregate functions. I think you would be better off without my 'voice' in this, so I can only &quot;butt out&quot;.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[JaneInMA]

Michael, please feel free to give advice- I am originally having problems with the union query but tblDCRMastered is one of the tables (the M of UNIONMOVA). I was just creating queries on the fly to debug it and was being lazy and working with the easier query. Your advice is great (in every post I see) I am just sorry that this thread has tried to follow my confusion as I has struggled to deal with the bugs I must have put in. Be easy on me....I just found a user putting in a date field for 8/22/2201 and another one who performed work on a document that did not exist at that time point they claimed, all as I started to debug this using the current data. So the queries arent working and the ones that are are returning data from outer space thanks to the users!
I just wish I knew why I can not get max to work with an union query.
SELECT Max(OpenWorkDays([qryUnionMOVA]![DCRReceivedDate],[qryUnionMOVA]![DateIssued])) AS maxtotyped
FROM qryUnionMA, qryUnionMOVA
WHERE (((qryUnionMA.DateIssued) Between #8/1/2001# And #8/28/2001#) AND ((qryUnionMA.Origin)=&quot;A&quot);

the query gives me 9, my clunky recordset gives me 16 and that is why I am resorting to viewing individual numbers.

 

[MichaelRed]

Well, I usually try the &quot;KISS&quot; method.

1.[tab]Verify the datatype for each column (If necessary on each of the seperate tables).

2.[tab]Check that the Min & Max & Avg & COunt functions return the correct value (again, on the individual table s if necessary).

3.[tab]combine the above to a Union Query.

If the table subset of the UNION query yields the correct answers then the union query should as well. MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over