Average formula on access

chivo123

Registered User.
Local time
Today, 11:43
Joined
Jan 20, 2012
Messages
21
Hey all,
I was wondering if I could be able to use the average formula in forms.
I have begin date, end date, agent name, and I would want to take the difference between those dates and returns me the average of calls. I was successfully able to return the difference dates, but I can't get the average to work. Here is what I use for the formula that I use:

IIF(=IIf([End Date]>[Date],DateDiff("d",[Date],[End Date])=Sum([Average wrap]),0). I put this formula inside TEXTBOX inside the form.
 
So, how do I choose the agent name under the query when I do the group by. It doesn't select the agent name.
 
You will need to be more explicit in your explanations because I don't know what tables you have there or what Agent Name relates to.

Did you follow the tutorial from start to finish?
 
Yes, I have already finished all the tutorials, and this is pretty expert question. So, I have a table named; Stats which it has all the variables for the employees. I have made a query based on that table that will give me the AGENT NAME, DATE, END DATE, AND AVERAGE WRAP, which I have already calculated for daily averages under my query. I have made a FORM that when I enter the DATE and END DATE and click on the agent name it pops out the daily average on span of those dates. The table already gives me the date difference, but it doesn't give me the Average wrap. My IIF formula works too, but somehow i think it doesn't return the characters under textbox format.
 
1. What purpose does the Date and End Date fields serve? What are they used for?
2. What do you mean by Average wrap?

NB: Date is a reserved keyword in Access/VBA. You shouldn't use Date as the name of your field. Also, don't put spaces in your field names.
 
Date and End dates are for the purpose that the user can select the Begin Date and End date to create the average of the stats that I have stored on the stats table. I have taken Average Wrap as an example to see how I can retrieve averages on subform. This is what I put inside the Textbox under control source: =IIf(Int(DateDiff("d",[Date],[End Date]))>1, Sum([Average wrap]),"Don't return anything").

Also, Average wrap is a formula that I used inside my query: (total outbound calls)+ (total inbound calls)/ (calls handled), and that returns the average daily wrap calls.
 
If i can't choose the name as part of my field, is there anyway I can be able to select those dates, and return me the average?
 
Also, how can the user select the date if I can't put it on the field.
 
You misunderstood my point. Rename your Date field to something else and don't put spaces in the names of any other fields.

Right, is there any need of having two Date/Time fields in your table? I don't think so. You only need just one. the BETWEEN code I gave you will go under just one Date/Time field and it will fetch all records where their date fall within the range. Did you get this point?
 
Alright, I understand now. I have just changed them. what should i do now?
 
* So you need only one Date/Time field.
* You can create a subform to display the corresponding AverageWrap or you can use the DLookup() function.
 
do i use the Dlookup under the control box of TEXTBOX?
 
But that does not return the average value between two dates. I want to create a subform when I click on begin date and choose the end date and be able to return average of those dates. I can't be able to select two dates with one date.
 
Chivo,

When you're asked a question on this forum or something is suggested you should ensure that you reply or make a comment to it. I requested that you upload a stripped down version of your database and you made no comment about it.
In your next reply, post a sample of your database so I can see exactly what you're doing. Here's how:

http://www.access-programmers.co.uk/forums/forumdisplay.php?f=23
It's the second time you've not answered to or made a comment about a remark I've made.
 
The problem is I can't be able to post it because it has private employee information. I would have posted it though. As I have said before, I will need begin date and end date so I can be able to select the data between the span of those dates. After selecting those dates I would want it to return the average of it. My query already returns the Agent Name individually, dates, but it doesn't return the average between the span of Begin Date and End Date.
 
We don't expect you to post confidential data. Enter some test data and post something. I need to see your setup because I'm working blindly here.
 
Okay, give me 5 minutes, so I can rebuild a basic template.
 

Users who are viewing this thread

Back
Top Bottom