Hi,

   Thanks for your help in advance.

   I am trying to do some date calculations. Here is some sample data:

'============================================
AppID AppName EventNo Date Time Event User
26 MSACCESS.EXE 5644 11/29/2004 3:28:05 PM Close mmcdonal
27 MSACCESS.EXE 5644 11/29/2004 3:27:57 PM Open mmcdonal
28 MSACCESS.EXE 61372 11/30/2004 7:43:44 AM Close mmcdonal
29 MSACCESS.EXE 61372 11/30/2004 7:43:31 AM Open mmcdonal
'============================================

You can see from this that I am tracking the opening of an application, and the closing of an application. The application has a single event number, so I know that each is the same instance of Access opening and then closing. I know the time the app opened, and the time the app closed.

I need to calculate, for many instances of Access for example (each with its own unique event number), how long access was open in a particular day. In this case, it was open for 8 seconds on the 29th, and 13 seconds on the 30th.

Perhaps this needs to be a module, so I will post this in Access VBA, but I will monitor both for posts, and update the other with solutions.

Thanks,



mmcdonal

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

qryOpen:

SELECT Table1.EventNo, Table1.Date, Table1.Time, Table1.Event
FROM Table1
WHERE (((Table1.Event)="Open"));

qryClose:

SELECT Table1.EventNo, Table1.Date, Table1.Time, Table1.Event
FROM Table1
WHERE (((Table1.Event)="Close"));

qryElapsedTime:

SELECT qryClose.EventNo, qryOpen.EventNo, qryClose.Date, qryClose.Time, qryOpen.Date, qryOpen.Time, [qryClose].[Date]+[qryClose].[Time] AS CloseDateTime, [qryOpen].[Date]+[qryOpen].[Time] AS OpenDateTime, DateDiff("s",[OpenDateTime],[CloseDateTime]) AS ElapsedTime
FROM qryClose INNER JOIN qryOpen ON qryClose.EventNo = qryOpen.EventNo;

Then you could group on Date and/or EventNo to sum the ElapsedTime

Hope this helps,


Clive Astley

There will be more than one application reported to the database, so I want to group on the AppName and then sum the total open time for each app. A final query result might look like this:

AppName TimeUsed User
Access 01:15:30 mmcdonal
NotePad 00:25:00 mmcdonal
Word 02:20:30 mmcdonal

Will this get me there? I am trying this now.

Thanks,

mmcdonal

Also, when I wrote the script, I changed the reported Date/Time to two entries. Should I keep them together in the same field when I report them so that I would get "11/30/2004 14:27:34" instead of "11/30/2004" and "02:27:34"?

IOW, can the query handle military time? If I push this with the proper mask, I think it will convert it anyway.


mmcdonal

Hi Michael,

I think you need to join the date and time fields in order to do the date arithmetic. But you would have to (a) either separate them or (b) use DatePart in order to group on the date for your reporting requirement.

I think Access will handle whatever format you throw at it. But VBA will not, if you decide to write a module. VBA works in mm/dd/yyyy but it also TRIES to convert anything else to this format. And sometimes gets it wrong! I personally always convert everything to yyyy/mm/dd when using VBA date arithmetic. Then it is under one's own control.





Clive Astley

Hi,

   Thanks for all your help! Here is what I did:

Open Query - your code - with AppName
Close Query - your code - with AppName
ElapsedTime Query: - slightly modified
'============================================
SELECT qryAppClose.AppName, qryAppOpen.AppName, qryAppClose.EventNo, qryAppOpen.EventNo, qryAppClose.Date, qryAppClose.Time, qryAppOpen.Date, qryAppOpen.Time, ([qryAppClose.Date]+[qryAppClose.Time]) AS CloseDateTime, ([qryAppOpen.Date]+[qryAppOpen.Time]) AS OpenDateTime, DateDiff("s",[OpenDateTime],[CloseDateTime]) AS ElapsedTimeSec, ([ElapsedTimeSec]/60) AS ElapsedTime
FROM qryAppClose INNER JOIN qryAppOpen ON qryAppClose.EventNo = qryAppOpen.EventNo;
'=============================================

Total Minute Query:
'=============================================
SELECT qryElapsedTimeRaw.qryAppClose.AppName, Sum(qryElapsedTimeRaw.ElapsedTime) AS SumOfElapsedTime
FROM qryElapsedTimeRaw
GROUP BY qryElapsedTimeRaw.qryAppClose.AppName;
'=============================================

This works perfectly. Thanks again. Now I can find out how much each user actually uses the apps per month.

Now all I have to do is push the last query results to a SQL server from the local hidden Access file.


mmcdonal

Glad you got it to work.


Clive Astley