Genereal, but complex Access 2007 Question

[hamd01]

Firstly I'll describe the scanario, we are looking to mointor the PC usage in our organisation. I have been able to create the following CSV files, and short section of which is below;


PCName User Time Date Event
B208-PROJ macg12 13:19:00 29/10/2009 LOGIN
BS101-09 20199186 12:15:00 29/10/2009 LOGOUT
CENLIB-01 20171740 12:33:00 29/10/2009 LOGOUT
CENLIB-01 20172295 12:42:00 29/10/2009 LOGIN
CENLIB-01 20172295 12:55:00 29/10/2009 LOGOUT
CENLIB-02 20207817 12:04:00 29/10/2009 LOGIN
CENLIB-02 20207817 12:14:00 29/10/2009 LOGOUT
LIB-02 20189295 13:01:00 29/10/2009 LOGOUT
LIB-02 20199806 13:03:00 29/10/2009 LOGIN
LIB-02 20199806 13:11:00 29/10/2009 LOGOUT
LIB-03 20197168 13:04:00 29/10/2009 LOGOUT
LIB-04 20201937 13:11:00 29/10/2009 LOGOUT
LIB-04 20201937 13:13:00 29/10/2009 LOGIN
LIB-05 20199877 12:24:00 29/10/2009 LOGIN
LIB-05 20199877 12:53:00 29/10/2009 LOGOUT
LIB-05 20199877 12:59:00 29/10/2009 LOGIN
LIB-05 20199877 12:59:00 29/10/2009 LOGOUT
LIB-05 20199877 13:09:00 29/10/2009 LOGIN
LIB-06 20199877 12:55:00 29/10/2009 LOGIN
LIB-06 20199877 12:55:00 29/10/2009 LOGOUT
LIB-06 20199877 12:56:00 29/10/2009 LOGIN


The actual file is huge with thousands of entries. The end result is that we have a CSV file at the end of every week, detailing all the LOGIN and LOGOUTS on each PC on our network.

Now...my question; Within access I would like to query this data, to be able to establish what % of use a PC had between 9am and 5pm between any 2 given dates.

So... if I ask for the usage between 5 March and 12 March it will calculate a total possible of 64 hours use (8 days * 8 hours), then from the CSV file calculate the time difference between the login events and the logout events for the relevant PC, total them all up, and calculate a percentage use for the period.

Hopefully all of the above makes sense to someone.

Im not by any means an Access expert, but before trying to learn how to do this I wanted to ask some expert if you think this is possible? And what might be my best way forward.

Thank you for any advice.

 

[gemma-the-husky]

yep - access is ideal for this purpose.

and you can do all you want, - relatively easily

just try importing the csv, using the wizard to split it into fields, and go from there. once you are happy with the data, you can automate the import prcoedure

try file/get external data, and go from there

note that when time is accumulated it will evaluate as fractional days - so your logged in usage might be 2.036 days. if you try to do it in hours/minutes directly you wont get very far, as time evaluates as the fractional part only. (ie 20hrs + 6hrs is 2 hrs! (26 hrs modulo 24hrs!)