how do you calculate averages days overdue in MS Access

[Tobasco1136]

I have a database, and need to calculate what the average days overdue files are. I have one report that shows when [DateFileDue]is greater than 30 days.

I have been tasked to figure out what the average amount of time for overdue files using [DateFileCheckedOut] [DateFileDue].
How can I calculate this?

 

[lespaul]

Maybe something like:

SELECT Avg(DateFileDue - DateFileCheckedOut) As AvgNumOfDays FROM TableName

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Tobasco1136]

I am trying to do this in an Access query. I couldn't figure out how to make your query work. Can I type this in the query criteria?

 

[lespaul]

No, you need to switch the query to the SQL view (View -> SQL) and paste the query above and make sure the field and table names are correct and then run the query.

 

[dhookom]

PMFJI,
Leslie's answer would be typed into the SQL view. If you understand the SQL you could design it in the GUI.

Make sure you use your field and table names since you didn't provide all of these in your original post.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tobasco1136]

First, thanks for the quick response.

This query will calculate on all records even if they were never overdue. I use a field DaysOverDue to calculate how many days the files are overdue. So I think the calculation should be on this field.

 

[dhookom]

jfakes,
I think it's time to stop guessing about your data and how you are storing values. Please consider entering some records (significant fields only) and how you would expect these to calculate an average.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Tobasco1136]

I have the table set up like this:

DaysOverdue: DateDiff("y",[DateFileDue],Now())+30

So now that I have DaysOverdue, how do I calculate the average DaysOverdue?

 

[VRoscioli]

As lespaul said, you need to use the Avg() function:

SELECT Avg(DateDiff("d",[DateFileDue],Now())+30) AS AvgDaysOverdue FROM tblTable;

If you only want to average values where DaysOverdue is positive, try:

SELECT Avg(DateDiff("d",[DateFileDue],Now())+30) AS AvgDaysOverdue FROM tblTable WHERE DateDiff("d",[DateFileDue],Now())+30>0;

-V

 

[lespaul]

DaysOverdue: DateDiff([b]"y"[/b],[DateFileDue],Now())+30

if you want the number of days overdue, why are you using the YEAR difference in the formula?

 

[dhookom]

The "y" interval is very confusing. "YYYY" would be the year difference while "y" is the Day Of Year. It seems from my experimenting that "y" is very much the same as "d".

From Help

To calculate the number of days between date1 and date2, you can use either Day of year ("y") or Day ("d").

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lespaul]

learn something new every day!

thanks Duane

Les