Max value from Query

Posted on 2013-09-04
Medium Priority
Last Modified: 2013-09-04
Hi Experts,

I am at a loss for a select query that produces times that employees punches for the day. What I am struggling with is filtering the results to that only the last punch of the day is selected.  An example would be like this:

UniqueID    EmployeeID       TimeOut

268372524  100596              1713
268372525  100596              2344

The first punch for time out is actually a transfer from one job code to another and I want to remove that from my query.

Any help is appreciated.

Question by:JJINFM
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 39464688
try this query

select t.*
from tableX as t
inner join
(select max(t2.timeout) as maxtimeout, t2.employeeid
 from tableX as t2
 group by t2.employeeid ) as t3
on t.employeeid=t3.employeeid and t.timeout=t3.maxtimeout


Author Comment

ID: 39464866
Thank you!  that did the trick.


Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question