datediff function

[Haz]

I have two fields called

stages (8 stages)
date ( the format is MM/DD/YYYY hh:mm:ss AM/PM)

each stage has corresponding date/time in the same record. I used a query for thse fields and group stages and average the date...thats why my poing is looking for average date/time between stages. The stages are data are like:

1-stage avg(date/time)
2-stage avg(date/time)
3-stage
to
8-stage



I couldnt think of a way in query or report to have his accomplished, but thought it could be done in vba coding. results will look like this:

1- stage to 2-stage = (avg(date/time) of 2-stage) - (avg(date/time) of 1-stage)

and so on......

I have thought of a way to have these stages as fields name and run horizontally but not good idea since the whole database can't be like that.

help will be much apperciated.....

 

[YevS]

Why dont you just make a crosstab query?

 

[DCrake]

Are you trying to create a time line? Trying to average 2 items is a bit inconclusive. If you are trying to to create an average over several records between two points that makes more sense. If you have stage 1, Stage 2 and duration in mins (DateDiff("m",[Stage1],Nz([Stage2],[Stage1])) as three columns in a query then average the duration column that will give you a productive figure.

I have included the Nz() wraparound to ensure that if no date is present in the cling argument the opening argument is used thus returning 0 not #Error.

David

 

[Haz]

Thanks alot guys, the crosstab query works out very well.

 

[Haz]

DCrake,

Just wondering how would you put that datediff funtion in 3 column query? becuase stage1, stage2, .....and so on are text values that I want to have them as field names and the cacluations for the dates under each stage.

Lets say stage 1 and 2 have 400 records of just the date/time . I want to be able to ue the datediff to average the time between the two stages.

and by the way is the datediff you wrote correct? becuase using it as expression is giving me error.

using a crosstab query gave me what I want except is I like to have the date listed under each stage as date/time not value. Crosstab quries required three crosstabs row heading, colum heading and value.

I hope I'm making sence.

 

[YevS]

Just make another query on the crosstab you made. His function has too many brackets.

(DateDiff("m",[Stage1],Nz([Stage2],[Stage1]))
should be
DateDiff("m",[Stage1],Nz([Stage2],[Stage1]))

query 1 = crosstab

query 2 = find the time between stages with datediff (check for nulls if you have to with nz)
datediff("m",[stage1],[stage2])

query 3 = find the avarage of all the differences

You could do 2 & 3 in the same query if you really want to.

 

[DCrake]

How can you calculate differences if as you say the vales are text values, not dates? Of course the DateDiff() won't work it is expecting dates. What do the values look like in your table? how is your table organised?

More info please.

David

 

[Haz]

Sorry I meant dates becuase when doing the crosstab query it turn the date into values. the table looks like this


field1 field2 field3 field4
StageType date location employee


stype type has those stages 1 through 8.