Solved

Generating Average time difference between 2 dates in MS Access (SQL)

Posted on 2004-10-15
7
365 Views
Last Modified: 2008-03-10

I have a table with this structure in Access database

tbl_issue
 - ID             (autonumber)
 - fldSTART       (Date/Time)
 - fldUPDATED     (Date/Time)


Now I need to find out the average difference between the 2 dates above
for all records. Question is how would I go about it ?
Thanks in advance.
0
Comment
Question by:vpekulas
7 Comments
 
LVL 8

Accepted Solution

by:
Eric Flamm earned 300 total points
ID: 12323637
SELECT Avg(DateDiff("d",[fldStart],[fldUpdated])) AS Expr1
FROM tbl_issue

This will give you the average in days ("d") - check help on DateDiff for more options.

-ef
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12323657
In a query, you'd need a field defined as:  (this will return minutes, if you want hours, change "n" to "h", days "d", etc)

AvgDifference:  Avg(DateDiff("n",fldSTART,fldUPDATED))
0
 
LVL 16

Expert Comment

by:Nestorio
ID: 12323667
Select avg(fldUPDATED - fldSTART) from YourTable

How do you want to express the average? (hours, minutes, seconds)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Nestorio
ID: 12323695
Select Format(avg(fldUPDATED - fldSTART), "dd hh:nn:ss") from YourTable
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12323697
SELECT Avg(DateDiff("d",[fldstart],[fldupdated])) AS Expr1
FROM tbl_issue;
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 12323702
wow... everyone at once.
0
 

Author Comment

by:vpekulas
ID: 12323967
Thanks guys, exactly what I needed :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question