Time Calculation Query Help Needed

S

S. Smith

I have been asked to create a database from a text file that
is being dumped from a card access system. The file creates
a simple table with a few fields:

Event ID (auto-numbered 1 through 100000) - Primary Key

Employee Name (Full Name with underscore between last_first)

Date

Time (Long format: e.g. 6:23:04 AM)

Area (e.g. Room 1 - Entry, Room 1 - Exit, etc.)


What I need to do is create a query that will determine the amount
of time that a person has been in a designated area. In other words,
I need to know when "employee X" entered "Room 1" and exited
"Room 1", based on the data in the given table.

Additionally this is supposed to be as automated as possible, so
anything that requires a lot on manual updating or changing is
not desired.

Is there a way to write an Access query that could determine that
someone spent X amount of time in "Room 1" based on the date
and their entry and exit times?

So far I have created make table queries that create separate tables
for the entry time and exit times, but am having trouble with the
relationships when trying to link the tables.

Is there a better way to do this? Cross tab query, maybe?

Thanks in advance for any suggestions or pointers to information.


- Scott
 
A

Allen Browne

You have a table where there is a record for each time a person enters a
room, and another record for each time they exit. You can create a query
like the one below that selects all the exits. It contains a subquery that
determines the most recent entry time for that person and room, and then
calculates the difference in minutes.

In order do do that, you may need to redesign your table:
1. Use a single field for the date and time combined. It's called
EventDateTime in the following example. (BTW, it's really important not to
use Date and Time as field names, as they are reserved words in VBA.)

2. Use a separate field for the MovementType ('Entry' verses 'Exit'), rather
than combining 2 things into one Area field (i.e. 'Room 1 - Entry', and
'Room 1 - Exit' should not be 2 records in the one table). That way you can
match the room they went into with the room that they left.

With those changes, you can create a query like this:

SELECT Table1.*,
( SELECT TOP 1 [EventDateTime] FROM Table1 AS Dupe
WHERE (Dupe.[Employee Name] = Table1.[Employee Name])
AND (Dupe.Area = Table1.Area)
AND (Dupe.EventType = 'Entry')
ORDER BY Dupe.EventDateTime DESC, [Event ID] DESC )
) AS EntryTime,
DateDiff("n", [EntryTime], Table1.EventDateTime) AS Minutes
FROM Table1
WHERE MovementType = 'Exit'
ORDER BY Table1.[Event ID]

Note that you may need to handle cases where the data entry failed. For
example, if Fred existed Room 4 at 10am Monday but his *entry* into the room
did not get recorded for some reason, the query will retrieve the previous
time he entered the room (probably some time in the previous week).

If subqueries are a new concept, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209066
 
Top