all services group by name

J

jenn

I have a database of names of people and when that person checks in for a
service their name is scanned in and the date and time is automatically
assigned and kept in the data base when the type of service is selected for
the visit...
do I need to query or group by in a report to list the visitor and their
services together for each date so that I can subtract the times of the
services to calculate the total times for each service
e.g.
jennifer 12/01/05 IN 09:00
Jennifer 12/01/05 OUT 09:30
Jennifer 12/01/05 In 10:50
Jennifer 12/01/05 OUT 11:00
I want to find each "jennifer" for unique dates and subtract the in from the
out and get the total time in the building
 
S

S Panja

How is the db set up? Are there separate scanners for IN and OUT populating
different fields?
--
***************************
If the message was helpful to you, click Yes next to Was this post helpful
to you?
If the post answers your question, click Yes next to Did this post answer
the question?
 
J

jenn

the fields are
name
service
and when you press ENTER after selecting the service the date and time is
added to the record... so when I look in the table I have Name, Service,
Date, Time
 
J

John Spencer

Assumption:
There is always a matching pair of in and out dates and times

Select A.Person, Sum( DateDiff("n",
(SELECT Max(ActionDate + ActionTime)
FROM YourTable as Tmp
WHERE InOut = "IN" AND
Tmp.Person = A.Person AND
Tmp.ActionDate + Tmp.ActionTime <= A.ActionDate + A.ActionTime),
A.ActionDate + A.ActionTime)) as Elapsed
FROM YourTable As A
WHERE A.InOut = "Out"
GROUP BY A.Person, A.ActionDate
 
Top