Need Help with SQL Statement

Results 1 to 2 of 2

Thread: Need Help with SQL Statement

  1. #1
    Join Date
    Dec 1969

    Default Need Help with SQL Statement

    &nbsp;<BR>I have a table with the following fields:<BR><BR>Participant_id<BR>status<BR>rank<BR >date<BR><BR><BR>New rows are constantly inserted into the table updating the Participant&#039;s new status and rank. I need to write a query that will allow me to bring back a recordset that has current status, former status and highest rank. The values for status and rank are numeric (1-5) I assumed I would use the max date but that is as far as I could get. Any help would be appreciated.<BR><BR>Thanks!<BR><BR>-matt<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Would this do it...

    SELECT Participant_id, status, [date] rankFROM table GROUP BY Participant_id, status, [date] ORDER BY [date] DESC<BR><BR>Then, the current stuff would be at the top, the previous would be record 2<BR><BR>Then do a rst.Filter = "MAX(rank)" or something like that.<BR><BR>HTH<BR><BR>Jerry<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts