var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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
Try something like the following (substitute highlighted text with actual table/field names):
SELECT T1.ID, T1.Rate1, T1.Rate2 T1.Date
FROM MyTable AS T1
WHERE T1.Date =
FROM MyTable AS T2
WHERE T2.ID = T1.ID
AND T2.Date <= [qryDate]
Two things I'd like to suggest though not related to your question.
- Don't have Date as field name b'cos its a SQL reserved word. you should find a listing here.
- 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
Not for each ID, HACK.
Originally Posted by HACK
Put <=[Forms]![YorFormName]![TextBoxName] in the criteria line of the query grid.
may be better with a group by clause