Solved

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

Posted on 2004-10-15
7
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

691 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