Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009
    Posts
    2

    Unanswered: Return Data based on Min(date) and Min(date)+1

    I need to pull the data from a table based on a person's two earliest/first two LeadDates and am having trouble figuring out how. The data is ordered in the table by StudID and LeadDate. There are a total of 34 columns, all of those need to be returned in the search.

    What is the correct MS Access SQL code that I need to use to retrieve the Desired Results as I have typed below. Thanks.

    Example Data:
    StudID Name LeadDate LeadSource Program
    98345 SJones 10/1/09 Referral MBA
    98345 SJones 10/1/09 Display MBA
    98345 SJones 10/5/09 WEBAD-1 MBA

    06731 KMartin 10/10/09 WEBAD-2 LibArts

    88435 WKay 10/23/09 Display-1 Nursing
    88435 WKay 10/26/09 WEBAD-1 Nursing
    88435 WKay 10/26/09 WEBAD-2 Nursing

    Desired Results:
    StudID Name LeadDate LeadSource Program
    98345 SJones 10/1/09 Referral MBA
    98345 SJones 10/1/09 Display MBA
    06731 KMartin 10/10/09 WEBAD-2 LibArts
    88435 WKay 10/23/09 Display-1 Nursing
    88435 WKay 10/26/09 WEBAD-1 Nursing

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This sounds like what you're after:

    ACC: How to Create a Top N Values per Group Query
    Paul

  3. #3
    Join Date
    Nov 2009
    Posts
    2
    Based on the above article and some help from another forum, below is the code that I ended up with to pull the absolute data that I needed...First two LeadDates (ie. Inquiries/Leads) per person. I actually had to use the system generated ID in the end in order to pull no more than TWO rows per person, specifically for those people who had multiple inquiries on the same day(s).

    SELECT MainData.*
    FROM SUO_Data_set AS MainData
    WHERE MainData.ID In
    (Select Top 2 [ID]
    From SUO_Data_set
    WHERE SUO_Data_set.SyStudentID = MainData.SyStudentID
    ORDER BY ID ASC)
    ORDER BY MainData.SyStudentID, MainData.LeadDate;

Posting Permissions

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