Access Data examination

[Jamie]

Hi,
I have a problem and this might not be the correct forum or even the correct
place to find an answer. If not please suggest recommendations of
(unfortunately free) where to find help.

I have a Access database that tracks computer usage. The problem is getting
the results I need after that. I need to be able to compare two PC's
together and get some information either when it was in use or even further
when both PC's are in use at the same minute.


I have written a query that extracts the data by machine (ComputerName) and
date (RecDate) if ActiveSec is not set to zero.

SELECT TrackData.UserID, TrackData.sTime, TrackData.eTime,
TrackData.ActiveSec
FROM TrackData
WHERE (((TrackData.ActiveSec)<>0) AND ((TrackData.RecDate)=#11/10/2003#)
AND ((TrackData.ComputerName)="U302KN8ZE652"))
ORDER BY TrackData.sTime, TrackData.RecDate;

This allows me to pick out the data for only one machine based on the day I
want to examine and I'm only interested in the Active time. I then view
this data in a report.

The problem is how the time is gathered and entered into the database. (I
can't change this it is part of the application which I have no control of)
I can only modify the data after it is collected.
Here is how the data is stored and displayed in a table.
ComputerName UserID sTime eTime UnActiveSec ActiveSec Sec isIdle
isNotEnd RecDate
U302KN8ZE555 ws11 11/4/03 12:31:55 PM 11/4/03 12:31:55 PM 0 0.031
0.031 FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 12:31:55 PM 11/4/03 12:31:55 PM 0 0 0 FALSE
FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 12:31:55 PM 11/4/03 12:31:55 PM 0 0.031
0.031 FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 12:31:55 PM 11/4/03 12:32:33 PM 0 37.672
37.672 FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 12:32:33 PM 11/4/03 12:57:10 PM 1477.516 0
1477.516 TRUE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 12:57:10 PM 11/4/03 12:57:11 PM 0 0.109
0.109 FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 12:57:11 PM 11/4/03 12:57:11 PM 0 0 0 FALSE
FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 12:57:11 PM 11/4/03 12:57:11 PM 0 0.016
0.016 FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 12:57:11 PM 11/4/03 12:57:21 PM 0 10.812
10.812 FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 12:57:21 PM 11/4/03 12:57:24 PM 0 2.875
2.875 FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 12:57:24 PM 11/4/03 12:58:59 PM 0 94.954
94.954 FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 12:58:59 PM 11/4/03 1:00:00 PM 0 60.75 60.75
FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 1:00:00 PM 11/4/03 1:00:21 PM 0 20.64 20.64
FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 1:00:21 PM 11/4/03 1:00:32 PM 0 11.766
11.766 FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 1:00:32 PM 11/4/03 1:01:04 PM 0 31.625
31.625 FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 1:01:04 PM 11/4/03 1:04:24 PM 199.906 0
199.906 TRUE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 1:04:24 PM 11/4/03 1:04:25 PM 0 1.406 1.406
FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 1:04:25 PM 11/4/03 1:04:31 PM 0 5.891 5.891
FALSE FALSE 11/4/2003
U302KN8ZE555 ws11 11/4/03 1:04:31 PM 11/4/03 1:04:33 PM 0 1.891 1.891
FALSE FALSE 11/4/2003


What I need is a 24hour period of every min that the machine was in use.
Then I compare two machines and look at both machines for when they may be
in use the same minute. I look at each sTime and eTime and I basically chop
off the seconds because the seconds are not important to me. If it is in
use from 12:00:59 to 12:02:01 Then it was in use for 3 minutes (see below).
12:00
12:01
12:02

So I have created an Excel spreadsheet with every min in it and I mark a 1
in the cell if it is in use. I put my machines data I want to compare next
to it the same way. I then create functions to examine if the two cells are
equal to 2 added together then I have a minute when they where both in use.
Can you see how long it takes me to manually create this 24hour period per
machine.

PLEASE HELP I'M OPEN FOR ANY SUGGESTIONS!