Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-10-15
7
Medium Priority
?
381 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 1200 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
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!

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 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