Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: Getting too many records from qry

    I have a table with this data:

    ID Rate1 Rate2 Date
    B 2.54% 6.00% 3/20/2008
    I 3.31% 6.00% 1/29/2008
    G 4.65% 6.00% 1/7/2008
    B 4.93% 6.00% 12/20/2007

    my qry is run from a form that will have a txt box qrydate,
    I want to select ONLY 1 ROW for each id where the Date is <= qryDate

    The table name is rates, I tried grouping and sorting desc by date and though ok on ly top 3, but in the future there maybe more ids.

    How do a get a result set droping the last date, i tried max of the date, but that returned all rows also

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    Try something like the following (substitute highlighted text with actual table/field names):
    Code:
    SELECT T1.ID, T1.Rate1, T1.Rate2 T1.Date
    FROM MyTable AS T1
    WHERE T1.Date =
     (SELECT MAX(T2.Date)
      FROM MyTable AS T2
      WHERE T2.ID = T1.ID
      AND T2.Date <= [qryDate]
     )
    ;

  3. #3
    Join Date
    Dec 2007
    Location
    India
    Posts
    16

    Exclamation

    Two things I'd like to suggest though not related to your question.
    1. Don't have Date as field name b'cos its a SQL reserved word. you should find a listing here.
    2. For date inputs text box is not a good choice, you should use Microsoft Date time picker (I assume you db is access)

    Database: MS Access 2003

  4. #4
    Join Date
    Dec 2007
    Location
    India
    Posts
    16

    Exclamation

    Will this not do the present job...
    Code:
    SELECT TOP 1 Rates.ID, Rates.rate1, Rates.rate2, Rates.dDate
    FROM Rates
    WHERE (((Rates.dDate)<=qryDate))
    ORDER BY Rates.dDate DESC;

    Database: MS Access 2003

  5. #5
    Join Date
    Feb 2004
    Posts
    137
    Quote Originally Posted by HACK
    Will this not do the present job...
    Code:
    SELECT TOP 1 Rates.ID, Rates.rate1, Rates.rate2, Rates.dDate
    FROM Rates
    WHERE (((Rates.dDate)<=qryDate))
    ORDER BY Rates.dDate DESC;
    Not for each ID, HACK.

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Put <=[Forms]![YorFormName]![TextBoxName] in the criteria line of the query grid.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    may be better with a group by clause

Posting Permissions

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