MIN MAX and Average

[cthai]

Ok - i know this should be pretty simple and striaght forward... but I can't figure out how to get this to work - in access 2007 - i have a table called userDatediff.

I use the date release MINUS date assigned to get the date diff (how many days it took the user to release the case)

i would like to have in the query to how the MIN/MAX and Average days it took the user to release the case... so in the column Date diff i have the number of days... I want to show the lease days John Doe took to release the case and the MAX days it took John Doe to release the case. then Average those days.

UserName Case	date_assigned Date_release date diff
John Doe pending  4/1/2011	4/20/2011     19
John Doe pending  4/2/2011	4/4/2011      2
				
				
				
				
John Do	Min	2		
	Max	19		
	Avg	10.5

 

[PWise]

use this query

Select username,min(date diff) as mindays,max(date diff) as maxdays,avg(date diff) as avgdays
from userDatediff
group by username

 

[cthai]

Hi PWise -

Thank you for the respoond - however the query above gave me the date different from the release date.
but it doesnt give me the MIN and MAX days for John Doe.

for example: I want to show what was the Lease Days it took John Doe to release a case (i.e 1 day) and what was the max days it took him to release a case (i.e 19 days)..

hope i am explaining this correct.

thanks again.

 

[PWise]

for the above data what would you like to see

or

why in the date diff colume the numbers 2 and 19 are right but in the query thay are not right

 

[cthai]

I would like to see -


John Doe Min = 2 days
John Doe Max = 19 days
John Doe Avg = 10.5 days

 

[cthai]

I apologize if I am not being clear and making this confusing... =)

so in the report that I am creating I want to show
username = min days
= max days
= avg days

i'm using =DMin("[username]","[date diff]")

but that's giving me an error.

 

[dhookom]

Calculate the datediff in the query that is the reports record source. You can then add text boxes in the UserName header or footer section with control sources like:

=Min([YourDateDiffFieldName])
=Max([YourDateDiffFieldName])
=Avg([YourDateDiffFieldName])

Duane
Hook'D on Access
MS Access MVP

 

[cthai]

right now in my table i have

UserName case	 date assigned	date released	date diff
John Do	 pending  4/1/2010	4/8/2010	7 days
John Do	 deny	  4/8/2010	4/9/2010	1 days
John Do	 pending  4/9/2010	4/20/2010	11 days
John Do	 pending  4/5/2010	4/11/2010	6 days
John Do	 pending  4/8/2010	4/15/2010	7 days
John Do	 pending  4/7/2010	4/13/2010	6 days
John Do	 Clear	  4/15/2010	4/25/2010	10 days
John Do	 pending  4/20/2010	4/28/2010	8 days
John Do	 pending  4/12/2010	4/19/2010	7 days

i want to show the MIN days in Date diff that it took John Doe to release the case (i.e 1 day) I also i want to show the MAX days it took John Doe to release the case (i.e 11 days) and the avg of the date diff for john doe (i.e John doe average 7 days)

 

[PWise]

and what does my query give you

 

[dhookom]

cthai,
Does your data actually display "7 days" or just "7". It should not have the "days" in it.

Did you add the text boxes that I suggested? If you place them in the UserName header or footer, they should display the correct values.

Duane
Hook'D on Access
MS Access MVP

 

[cthai]

Hi PWise -

the query you gave me shows this

date released	date diff  mindays  max days  avgdays
4/8/2010	    7	     7	       7       7
4/9/2010	    1	     1	       1       1
4/20/2010	    11	     11	       11      11
4/11/2010	    6	     6	       6	6
4/15/2010	    7	     7	       7	7
4/13/2010	    6	     6	       6	6
4/25/2010	    10	    10	      10	10
4/28/2010	    8	     8	       8	8
4/19/2010	    7	     7   	7	7

and here is the code

SELECT userDatediff.username, userDatediff.[Date Diff], Min(userDatediff.[date diff]) AS mindays, Max(userDatediff.[date diff]) AS maxdays, Avg(userDatediff.[date diff]) AS avgdays
FROM userDatediff
GROUP BY userDatediff.username, userDatediff.[Date Diff];

 

[randy700]

cthai,

Your last post doesn't make much sense.
The code you claim to be using begins with SELECT userDatediff.username
but the results panel shows date released.


Randy

 

[PWise]

1)Sorry but you are not using my query
please use my query and i think you wil get the result that you want

2) the results that you show are not from the query that you show