Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 20th, 2003, 05:02 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Whitstable, , United Kingdom.
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.
Reply With Quote
  #2 (permalink)  
Old September 20th, 2003, 12:10 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: Houston, Texas, USA.
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
Reply With Quote
  #3 (permalink)  
Old September 20th, 2003, 01:52 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 12:37 PM.


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