Average of a calculated fields in Queries

[MSLamoureux]

Hi,

I've been trying to insert a field that would calculate the average of another calculated field in a queries.

So I have a field name "ReceptionDate" and "AppointmentDate"

I would like to have a queries that would in one field named "Delai" calculate de delai between these two dates

and in another field named "AverageDelai" that would calculate the average of the results found in the field "Delai".

I've been trying few things different ways but I'm always confronted with the problem of wanting to use a calculate field on a calculated one and it just wont do it.

Would somebody have any idea? May be I'm just doing all this wrong? But I need to have the information about the delai between the reception date and the appointment date and the average delai.

 

[jdraw]

Show us what you tried.
Create an example or two with the dates and the values you want.
Help us help you by providing more info.

 

[Beetle]

A calculated field in a query is used to calculate values on a row by row basis. This would be fine for calculating the difference between ReceptionDate and AppointmentDate for each record. You could use DateDiff for this, i.e.;

Delai: DateDiff("d", [ReceptionDate], [AppointmentDate])

However, if you want to calculate something (like an Average) based on all the rows (or at least a group of rows) of a record set (table or query) then you do so from outside the source (your query in this case). For example, you could use the Domain function DAvg in an unbound text box on a form;

DAvg("[Delai]", "[YourQuery]")

Or, if the results of your query are displayed in a form, you could put an unbound text box in the footer of the form and use the Avg function in the Control Source of the text box;

=Avg([Delai])

 

[MSLamoureux]

A calculated field in a query is used to calculate values on a row by row basis. This would be fine for calculating the difference between ReceptionDate and AppointmentDate for each record. You could use DateDiff for this, i.e.;

Delai: DateDiff("d", [ReceptionDate], [AppointmentDate])

However, if you want to calculate something (like an Average) based on all the rows (or at least a group of rows) of a record set (table or query) then you do so from outside the source (your query in this case). For example, you could use the Domain function DAvg in an unbound text box on a form;

DAvg("[Delai]", "[YourQuery]")

Or, if the results of your query are displayed in a form, you could put an unbound text box in the footer of the form and use the Avg function in the Control Source of the text box;

=Avg([Delai])

@Beetle
That's exactly the problem I think.
I'd actually been able to get the calculated field "Delai" with this formula
Délais:Round(DiffDate("j";[DateEngagement];[DateRendezVous])/7*5)

Witch provide me the delai by day between the two dates and now I need to know the average delai so the average numbers of day people must have waited between the day they have there engagement and the date of there appointment.

I've never use the domain fonction and yes I want to calculate the average based on all the rows of a query. So I will have to take the time to clearly translate and understand your suggestion and give it a try. I had the feeling that I had to calculate it outside the query but I just didn't know how.

 

[MSLamoureux]

Show us what you tried.
Create an example or two with the dates and the values you want.
Help us help you by providing more info.

I'd tried so many things. First I managed to create a field inside the form where the different date are filled but I can see by reading here that we shouldn't put a calculated field into a form. But anyway it wasn't working.
I had first created the field TempsAttente and wrote this formula in the control line : =Round(DiffDate("j";[DateEngagement];[DateRendezVous])/7*5)

I surely had the appropriate result in the field but I couldn't make any sorting or use it in a query. I guess because the formula was directly in the field.

I also tried to write the formula in the code like this: ( I'm a newby... trying to make something by trying to understand others work )

Private Sub TempsAttente_AfterUpdate()
Me!TempsAttente = Round(DateDiff(d, DateRendezVous, DateEngagement) / 7 * 5)
End Sub

Wishing to see the result in the field TempsAttente of the form.
It didn't have any effect.

So after that I tried to make a query with the formula DateDiff ( as shown) and now that's where it lead me

I feel like a pupy running after his tail

 

[MSLamoureux]

A calculated field in a query is used to calculate values on a row by row basis. This would be fine for calculating the difference between ReceptionDate and AppointmentDate for each record. You could use DateDiff for this, i.e.;

Delai: DateDiff("d", [ReceptionDate], [AppointmentDate])

However, if you want to calculate something (like an Average) based on all the rows (or at least a group of rows) of a record set (table or query) then you do so from outside the source (your query in this case). For example, you could use the Domain function DAvg in an unbound text box on a form;

DAvg("[Delai]", "[YourQuery]")

Or, if the results of your query are displayed in a form, you could put an unbound text box in the footer of the form and use the Avg function in the Control Source of the text box;

=Avg([Delai])

Hi again,

Ok I've been trying this:

My query DelaiAttente
In one field I have : TempsAttente: Round(DiffDate("j";[DateEngagement];[DateRendezVous])/7*5)

That point works fine.

Now I've tried to put and unbound text field in a form and in the source control:

DAvg([TempsAttente],[DelaiAttente])

and it send me a message error so I've changed it for:

DAvg([TempsAttente];[DelaiAttente])

And it doesn't work it keeps writing "Name?" in the field instead of the answer.


I guess I made a mistake somewhere.

 

[Beetle]

The arguments in DAvg are strings, so they need to be enclosed in quotes;

=DAvg("TemsAttente", "DelaiAttente")

 

[MSLamoureux]

The arguments in DAvg are strings, so they need to be enclosed in quotes;

=DAvg("TemsAttente", "DelaiAttente")

Ooops!

Thank you soooo much!! That works just fine!!