Time Calculation Query Help Needed

  • Thread starter Thread starter S. Smith
  • Start date Start date
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
 
Well, I know how I (non-expert) would approach this. First I would create a
select query that contains all the fields
in your table except the EventID. I would click the Totals button on the
toolbar and allow the Totals Row to make all the fields except Time "Group
By" and for Time I would select "First".
Then I would name the query, close it, and copy it. In the copy I would
change the Time field from "First" to "Last", name the query and close it.
Now I have entry and exit times in two separate queries.
In a 3rd query, I would include all the fields from the first query and just
the LastOfTime from the second query.
I would create Left joins between the EmpName, Date, and Area fields.
I would create a calculated field called Elapsed like this:
Elapsed: DateDiff("n",[FirstOfTime],[LastOfTime]) that will give you elapsed
time in minutes.

Hope that helps.


Ed G
 
Back
Top