all services group by name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jenn - 07 Aug 2006 20:02 GMT

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

Reply to this Message

S Panja - 07 Aug 2006 20:43 GMT

How is the db set up? Are there separate scanners for IN and OUT populating
different fields?

Signature

***************************
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?

> 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
[quoted text clipped - 10 lines]
> I want to find each "jennifer" for unique dates and subtract the in from the
> out and get the total time in the building

Reply to this Message

jenn - 07 Aug 2006 20:47 GMT

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

> How is the db set up? Are there separate scanners for IN and OUT populating
> different fields?
[quoted text clipped - 13 lines]
> > I want to find each "jennifer" for unique dates and subtract the in from the
> > out and get the total time in the building

Reply to this Message

John Spencer - 07 Aug 2006 21:11 GMT

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

>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
[quoted text clipped - 12 lines]
> the
> out and get the total time in the building

Reply to this Message