Need help bringing back an ID on the most recent record

I have a table with

Student ID             EventID             EventDate  (for example)
573                        2670                7/1/2007
573                        2716                7/1/2006


I'd like to return the EventID of the last EventDate for each Student.

I've tried many variations of Max and Last, but because I want to
bring back the EventID, I keep doing a GROUPBY on EventID and then I
get multiple rows instead of just the most recent row.

In the example above, I'd want to return

573, 2670  (so that I can join event 2670 to the event table to find
out more about it).

But I keep getting both records returned.

Can anyone help with this query?

Thanks
LD

0
ldmccarty
8/15/2007 12:38:36 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1129 Views

Similar Articles

[PageSpeed] 4

I find that the easiest way to do this is via two steps:
First, a query to return the latest EventDate for each StudentID (call it 
qryLatestEvent):
    SELECT StudentID, Max(EventDate) AS MaxOfEventDate
    FROM YourTableName
    GROUP BY StudentID;

Then join this query to the original table, on both StudentID and EventDate, 
to get the EventID:
    SELECT YourTableName.StudentID, YourTableName.EventID, 
YourTableName.EventDate
    FROM YouTableName INNER JOIN qryLatestEvent
    ON (YourTableName.EventDate = qryLatestEvent.MaxOfEventDate)
    AND (YourTableName.StudentID = qryLatestEvent.StudentID);

HTH,

Rob

Maybe some query guru will show you how to do this in a single query, 
involving sub-queries; however, I find this two-step process much easier to 
follow what's going.

<ldmccarty@earthlink.net> wrote in message 
news:1187138316.655718.291700@g12g2000prg.googlegroups.com...
>I have a table with
>
> Student ID             EventID             EventDate  (for example)
> 573                        2670                7/1/2007
> 573                        2716                7/1/2006
>
>
> I'd like to return the EventID of the last EventDate for each Student.
>
> I've tried many variations of Max and Last, but because I want to
> bring back the EventID, I keep doing a GROUPBY on EventID and then I
> get multiple rows instead of just the most recent row.
>
> In the example above, I'd want to return
>
> 573, 2670  (so that I can join event 2670 to the event table to find
> out more about it).
>
> But I keep getting both records returned.
>
> Can anyone help with this query?
>
> Thanks
> LD
> 


0
Rob
8/15/2007 2:42:54 AM
Reply:

Similar Artilces:

Help
Hope someone can see the error of my way. I have a workbook saves as a template. Each week I receive a new excel file (data dump) from payroll timesheets of all the employee's hours charged to the different job codes and projects they worked on for the week. With this file open, I run the below macro. I only use two colunms, job numbers and hours from the original file. I copy the two columns, then open the template, paste the data into the newly created file, input the week ending date in a cell (that cell is referenced in the chart title), then do a save, then ask for it to calculat...

SRS to open Record of Focus
Hello All! Customer needs to use SRS to open custom quote while in CRM Quote Form. I have added button that points to custom SRS Report, but don't know how to pass the info from the quote of focus to let SRS know which customer record to display when running the report. I believe I can prompt for this parameter in SRS, but this would be an extra user step; as they already have the quote form open. Thought about src attribute, but SRS isn't looking for url... Any ideas? Hi, There is already a facility for reports which you can use instead of creating it yourself if I understo...

The Key Management Server Database operation failed. Microsoft Exchange Administrator ID No: c1031d9f
This is a multi-part message in MIME format. ------=_NextPart_000_0153_01C42D68.FA03AC90 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Environment:=20 W2K Active Directory Domain running in native mode (SP4) 2 domain controllers both running Exchange 5.5 with (SP4) Exchange 5.5 Internet Mail Service is running on the secondary domain = controller IIS 5.0 with SMTP services running on primary domain controller relaying = Internet Mail to Internet Mail Service on secondary domain controller. Certificate Server Root is on the primary ...

need to add/remove characters over thousands of cells
I have thousands of payroll records that are imported into excel on a daily basis. The cost codes appear like this: 7002/1045.824240305 The 7002/ needs to be removed and a . (dot) inserted five from the end like this: 1045.8242.40305 Please note there are hundreds of different cost codes so I cannot simply copy and paste but they must all be coverted so the format is the same. I am desperate for suggestions. cheers, Nadia Try this formula: =MID(A1,FIND("/",A1,1)+1,LEN(A1)) Then use a custom format to get the dot in there. ******************* ~Anne Troy www.OfficeArticles.com www....

Removing Windows Live ID from file
I need to remove Windows Live ID to export My Money file. The usual process (File/Password Manager/Remove/Next) gives a pop-up saying that "Windows Live ID (email and password) was not removed -- please try again." I've gone to settings to turn off online updates. I'm instructed by Money Help to "Click Receive account updates from MSN Money My Accounts." That heading doesn't exist on my setting page. Any clues why I can't remove Windows Live ID, or any other routes to attempt? I can't open the exported files unless this is remove...

How to duplicate records in a subForm? 1)Copy&Paste?? 2)Programmatically??
We have a Form with a subForm We want to create a subForm Record and then duplicate it "x" times. 1 - How can we just copy and paste the record while looking at the Form & subForm? 2 - How about code to programmatically duplicate the subForm record "x" times ? thanks for any help. Mel On 3 May 2007 14:08:29 -0700, Mel <MyEmailList@gmail.com> wrote: >We have a Form with a subForm > >We want to create a subForm Record and then duplicate it "x" times. Why? Just store a number field X in the record, and use that in a query to DISPLAY x cop...

HELP- Copy a Data Series to Other Graphs?
Hi- I'm using Excel 2002 and I've got a lot of XY (Scatter) graphs. When I add a new data series to one graph, it is defined by 3 values (Name, X Value and Y Value) If I want to add this data series to 7 or 8 add'l graphs, I know that I can copy/paste these 3 values to each of them. Is there a simpler way to accomplish this? thnx ...

repeating calendar entries moves 1 hour back in time
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have a mac pro wit osx 10.6.2. I run entourage and sync with a pc running xp and outlook and an ipod touch. every thing is fine. except for one thing: when I enter a repeating event in the calendar (every day) the event moves ahead of time that is from say 16.00 to 15.00 and some time later it beebs the alrm at 14.00. <br> cannot find a fx on this, and its only in entorage this happens <br> any help? <br> kind regards <br> Palle Which version of Entourage are you running? and what...

Entourage help 12-28-09
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Moved to Office 2008 and use Entourage for email. Was crashing quite often, call Microsoft and rebuilt data base and then crashes weren't so often but still crashed a couple of times a day. Microsoft called and suggested uninstalling Office 2008 and then re-installing. I questioned them about losing data, said no problem. So I did and now it won't open at all, seems there's a data base problem. Spent a couple of hours on phone trying the changing Office 2008 Identities from Ti...

What Do I Need To Learn Beside C Sharp To Make a GUI That Communicates To A Machine???
Could someone please give me some direction? I want to create C# GUIs using Microsoft Visual Studio because I like the way they appear. (I do not know how to program in C# yet.) The GUIs I create on one computer will need to communicate with a machine that has an on-board computer running a C Program in the LINUX operating system. The machine will plug into a router or switch using a standard cat5 cable/ethernet connect on a local subnet where the GUI computer will also have access to the internet. The GUI will store a list of variables and their values and then issue a sequence o...

New to MSCRM 3.0 Please help
Can someone clear something up for me and point me to a document that will help. I have installed an MSCRM 3.0 server and when I log in to https://servername:5555 I get a gui that allows setup but looks more like an admin site than a CRM user interface, am I doing something wrong. Please excuse my ignorance. -- Moose Hi Moose It sounds to me like you need to assign yourself some CRM 'user' rights in addition to the System Administrator rights. I take it that you're using the same user ID that you used to install the CRM? If that's the case, go into User Manager and ...

Going back to the start of a string
Hi, simple I'm sure but can't see the answer. Currently going through a string "DO While Not ts_IN.AtEndOfStream" looking for information. This includes then checking another string (while inside the first) for matching reference point for further data. So within the first loop, I have a second loop "Do While Not ts_IN2.AtEndOfStream". And looping through that string searching for the reference point. This works initially, but after awhile don't find anything because it is not going back to the start of ts_IN2 string each time loops on the fir...

Event ID 623 #2
I get ESE throwing a 623 error. KB 288700 says to disable write caching, but write caching has already been turned off. This is on exchange 2003. Any other ideas? The quick, short term, work around is to restart the store. The long term solution is to adjust the version store memory. The version store is where transaction are held in memory until they can be written to disk. If something is preventing us from completing transaction or writing to disk we will consume this cache and the store will stop responding to request until there is room in the cache again. Changing the w...

Integration Mgr 8.0 Help!!
We are using GP 7.50 and Integration Mgr 7.5 but we wanted to upgrade to Integration Manager 8.0. Can you use Great Plains 7.5 with Integration Manager 8.0? Is anyone aware of any problems with doing this?? Your input would be appreciated. Thanks Barb Integration manager has adapters for 7.5 and 8 And yes, you can have both adapters active in IM but you would only use one destination HS "BK" <BK@discussions.microsoft.com> wrote in message news:14862B2D-6478-4FD2-8F05-78D80581969D@microsoft.com... : : We are using GP 7.50 and Integration Mgr 7.5 but we wanted to upg...

Going BACK to Money 2006 from Money Plus....
Ok, I've given M+ the boot. I bought the CD after a decent trial period and have decided to switch back to Money 2006 Deluxe because... 1. Can't use Spending Tracker cuz it slows down EVERYTHING...MS Support says it is an unresolved issue. 2. Activation..... 3. Nothing extra except for INSIGHTS and Activation...just a relabled MSMoney 2006/2007. So as it is advocated, I supported M$ by buying the newest version....but ain't using it.....Grrrr!!!!! I hope Money 2009 is that overhaul that is overdue. -- Glenn B. Once activated, why do you care about activation? I agree th...

WEB help in Excel
In the HELP window of Excel there are several topics titled "WEB: ....". But when I select one of these, I get the message "This Web page is unavailable" from "Office Online". Example : 1) I click the Index tab. 2) I type the keyword "shortcut". 3) I click the topic "WEB: Working with the Office Assistant in Office 2000". 4) I get "This Web page is unavailable" etc. The URL that cannot be found is http://officeupdate.microsoft.com/2000/focus/articles/ofassist_aw.htm?ver=19990826 How can I repair this ? Im using Office 2000 on Windo...

How to delete "non-identical" duplicate records in an Access table
How to delete "non-identical" duplicate records in an Access Table? Where "non-identical" duplicate record means a record in the table that has slightly different datum in one of the fields, but an identical duplicate datum in the field that I am concerned with. For example: SSN MRN CLIENT NAME 001-00-2222 11170419 Smith, Jane 001-00-2222 11170419 Smith, Jane T 001-00-2222 11170419 Smith, Jane Thompson The data of these two records in the fields SSN and MRN are identical; but "non-identical" in the CLIENT NAME field (notice...

HELP!!!!
Deployment Manager does not work! After installation with no problems I go to Deployment Manager to add a user and I get this either when I click "refresh" or try and add a "new user" "The sever localhost is not responding. This might happen if it is currently unavailable, it is not a Microsoft CRM server, or you do not have sufficient privledges to run User Manager. For more information, contact your system admin." (Win2003, SP1, MS CRM 1.2) I am logged in as the administrator and the Deployment Manager can see the SQL server (same box) yet I cannot a...

Help! I can't get a spreadsheet to open
Have tried EVERYTHING - it keeps saying that I don't have access - AND I was the one who created it - any suggestions? Excel stores in Windows\Temp(win98) folder a copy(s) of any file that is open. Sometimes this *.tmp file is not deleted from the Temp folder when you close out Excel(maybe a crash of Excel?) or the file, so Excel thinks it is still open and in use. You can try a re-boot and see if that clears it up. If not, after a re-boot, clear out your Windows\Temp folder. If using WinXP your Temp folder would be under user....... Documents and Settings\User\Local Settings...

Help! Extract the subject of all mails in outlook to a text file
How can I extract the information, such as subject, sender, time of all the mails in my Inbox into a text file? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Switch to a table view that contains all the fields you need the information from, select all items, and press CTRL+C to copy. You should be able to paste the data in columns into a text file. Note that not all available fields will copy in this manner, but the ones you mentioned should work. -- Jocelyn Fior...

Cannot use update query to update records in another query
I need to update a field in one table with data from another table. I have joined the two tables in a query, but when I try to update the field using an update query or using VBA, I get an error saying that the query is not updatable. I have checked several other queries that join multiple tables and none of them will allow me to update any of the fields. Any thoughts? Thanks in advance for your assistance, Dan WarEagle90 wrote: > I need to update a field in one table with data from another table. > I have joined the two tables in a query, but when I try to update the > fiel...

Help with macro looping and color query function
I have been struggling to get a macro together to perform some tricky coloring functions. With the kind help of a number of people in the 'Excel Worksheet Forum' I have got close to achieving something which works but need help to get the macro to run. Here is where I have got to: The following is an example macro, kindly provided to me by Paul who has been helping me in the worksheet forum: Sub Macro1() ' ' Macro1 Macro ' Macro recorded 19/05/2005 ' ' mycell = Range("A1").Value mycolor = Range("C1").Value Sheets("Sheet2...

Need Excel equivalent of Lotus CTRL-SHIFT-PG UP/DWN #3
LOL no that's not it! Watch this.....I have Sheets 1 to 5 and all have the exact same formul in A1 so I want to highlight Sheet1:A1 to Sheet5:A1 then just paste i those 5 cells. Or just type something in Sheet1:A1 then hit ENTER an have it jump to Sheet2:A1 and so on. That's the best way I can explain it. If you used Lotus you woul immediately know what I meant. Thank -- vpr8 ----------------------------------------------------------------------- vpr80's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1578 View this thread: http://www.excelforum.com/sho...

Help! Tiled printing problem
Hi all, This is driving me crazy. I've designed a business card in Publisher. I'm trying to print using the tiled command, so I get 10 on the page (A4). I check the appropriate box in publisher, but when I click print it keeps printing only one in the centre of the paper. Sometimes the checkbox will even change from tiled printing to one in the centre after I've clcked the print button! I just tried it again on some spare paper (before I use photo paper) and it printed correctly, but then the next time it only printed a single card in the centre again. Any ideas? Windows XP,...

Help with the SP.
I am try to search for a date like 01/01/1900 but it does not. I found this sp on web site and wonder if you can help debugging the probblem. I try to run the sp to return all the columns and tables that have 01/01/1900 values in the tables. --SQL2008 Thank you. IF OBJECT_ID('dbo.usp_SearchAllTables', 'p') IS NOT NULL DROP PROCedure dbo.usp_SearchAllTables GO CREATE PROCedure dbo.usp_SearchAllTables ( @SearchStr VARCHAR(100) ) AS BEGIN -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. -- Purpose: To search all columns o...