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 September 20th, 2003, 05:02 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 121
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL syntax for my query - guru needed

I am a beginner and need help with an Access SQL statment.

I have a simple Access table with 3 columns in it. They
are UserId, UserName, UserDate. All 3 fields form a combination
key - so we can have records with the same UserId and
UserName but unique UserDate values.

The question: what is the SQL syntax to retrieve the
records for each UserId based on the latest UserDate
for each record? (ie one record per user that has the
latest date). Any help appreciated.
 
Old September 20th, 2003, 12:10 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 174
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Replace tblUsers with the proper name of your table in all instances and this would provide unique values with the max date:

SELECT tblUsers.UserID, tblUsers.UserName, Max(tblUsers.UserDate) AS MaxDate
FROM tblUsers
GROUP BY tblUsers.UserID, tblUsers.UserName;

Regards,

Beth M
 
Old September 20th, 2003, 01:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Your specification doesn't make it clear that there is more data in each row besides the 3 key columns. The second paragraph seems to indicate that the 3 columns are all that there is, and if that is so, then Beth's solution above is correct.

But, your third paragraph asks to retrieve the "...records for each UserId based on the latest UserDate for each record...", and I read that to mean there is more data in the row besides those 3 columns, and you want to retrieve the whole row. To identify the row that contains the maximum UserDate for each user requires what's called a 'correlated subquery' - this is a query which gets executed for each row in the outer, containing query:
Code:
SELECT UserID, UserName, UserDate, ... <theothercolumns> ... FROM yourtable AS T1
    WHERE T1.UserDate=
        (SELECT MAX(UserDate) FROM yourtable
            WHERE UserID=T1.UserID AND UserName=T1.UserName);
BTW, I would guess that your UserID and UserName columns refer to the same User; if this is so, then you are unnecessarily duplicating information, and your table design violates so-called 2nd normal form. This is a principle of database design which states that redundant information should be extracted into it's own table, and a relationship between the two tables defined. Thus, you should perhaps have one table defining UserID and the associated UserName, and another which lists the UserDate data for each UserID along with any other information pertaining to the UserID and Date combination.

Database normalization is not a set of hard and fast rules, but rather guidelines which experience has shown reduces all sorts of problems when data has to retrieved or updated.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Query Syntax hugh@kmcnetwork.com SQL Language 1 May 1st, 2007 06:42 AM
Syntax error in query. Incomplete query clause. dispickle ADO.NET 3 April 16th, 2004 01:04 PM
Syntax Needed SerranoG Access VBA 4 February 6th, 2004 06:41 PM
Word VBA Syntax Needed SerranoG VB How-To 4 October 28th, 2003 02:16 PM
Procedure Calling Syntax Needed SerranoG Access VBA 4 October 24th, 2003 07:03 AM





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