Return Greatest and Least Values?

G

Guest

Hi there,

I have a table that has the following layout:

Date EmployeeID SignIn SignOut
------------------------------------------------------
120204 123456 8:00 AM 9:00 AM
120204 123456 9:10 AM 11:00 AM
120204 123456 12:00 PM 5:00 PM

Notice we are looking at the same employee ID for a single date, and that an
employee may SignIn or SignOut multiple times during a given day.

Now, what I want to do is retrieve the earliest SignIn time and the latest
SignOut time to get the following result:

Date EmployeeID SignIn SignOut
 
M

Marshall Barton

simsjr said:
I have a table that has the following layout:

Date EmployeeID SignIn SignOut
------------------------------------------------------
120204 123456 8:00 AM 9:00 AM
120204 123456 9:10 AM 11:00 AM
120204 123456 12:00 PM 5:00 PM

Notice we are looking at the same employee ID for a single date, and that an
employee may SignIn or SignOut multiple times during a given day.

Now, what I want to do is retrieve the earliest SignIn time and the latest
SignOut time to get the following result:

Date EmployeeID SignIn SignOut


II think this will get you going:

SELECT datefield, employeeID,
Min(SignIn) As MinIN, Max(SignOut) As MaxOut
FROM thetable
GROUP BY datefield, employeeID

but you will probably want to add a WHERE clause to restrict
the date and/or the employeeID.
 
G

Guest

That worked great. Thanks!

Now what if I wanted to retrieve the same data for multiple dates? For
instance, all historical dates for one employee? Any ideas?
 
M

Marshall Barton

As I said before "add a WHERE clause to restrict the date
and/or the employeeID"

SELECT datefield, employeeID,
Min(SignIn) As MinIN, Max(SignOut) As MaxOut
FROM thetable
WHERE EmployeeID = 1234
GROUP BY datefield, employeeID
--
Marsh
MVP [MS Access]


That worked great. Thanks!

Now what if I wanted to retrieve the same data for multiple dates? For
instance, all historical dates for one employee? Any ideas?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top