Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 31st, 2004, 10:26 PM
Registered User
 
Join Date: Aug 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Max() and Min() Functions Help need...

Table
USERID NAME Date Time
1 D.A.U.Nishantha 24/8/04 8:20:00 AM
2 D.A.U.Nishantha 24/8/04 5:51:00 PM
3 M.J.Dharmakeerthi 24/8/04 8:14:00 AM
5 M.J.Dharmakeerthi 24/8/04 9:03:00 PM
7 M.S.Asanka 24/8/04 7:59:00 AM
7 M.S.Asanka 24/8/04 5:01:00 PM
8 W.S.Francis 24/8/04 8:20:00 AM
9 W.S.Francis 24/8/04 8:14:00 PM


Query
SELECT USERINFO.USERID, USERINFO.NAME, CVDate(DatePart('d',[CHECKTIME]) & '/' & DatePart('m',[CHECKTIME]) & '/' & DatePart('yyyy',[CHECKTIME])) AS [Date], TimeValue([CHECKTIME]) AS [Time]
FROM CHECKINOUT INNER JOIN USERINFO ON CHECKINOUT.USERID = USERINFO.USERID
WHERE (((CVDate(DatePart('d',[CHECKTIME]) & '/' & DatePart('m',[CHECKTIME]) & '/' & DatePart('yyyy',[CHECKTIME])))=Date()));

in this above table each employee has 2 records.i need to display records with maximum time in a seperate query and minimum time values in seperate query. how can i use Max ans Min functions in this above query...please help me....
Regards
shana



 
Old September 7th, 2004, 09:22 AM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello Shana!
i have tried to understand ur question and i have opened a query grid based on a table similar urs. Then i have selected Totals from the menu , Count for the ID field, Max for the Time field and i have come to a query where only the Max values where shown for each name.The SQL for the query was:
SELECT TOP 100 Count(Shana.ID) AS CountOfID, Shana.Name, Shana.Data, Max(Shana.Hora) AS MaxOfHora
FROM Shana
GROUP BY Shana.Name, Shana.Data
ORDER BY Max(Shana.Hora);

Hope this will help u.
Regards Penta.






Similar Threads
Thread Thread Starter Forum Replies Last Post
MAX/MIN Value Help slbibs SQL Server 2005 4 November 6th, 2007 04:00 AM
SQL script for selecting min and max values Nancy Krause Need help with your homework? 0 April 12th, 2007 11:29 AM
MAX and MIN ON TD cleytonjordan XSLT 7 July 27th, 2005 12:39 PM
SQL Max() and Min() functions help shana Oracle ASP 1 April 25th, 2005 08:34 PM
How to use Min,Max,Abs functions braindog_43 XSLT 0 February 7th, 2005 06:04 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.