Select Min value and its date from Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with 2 fields. The first is filled with dates and the second values
How do I select the Minimum of the values and the date which this relates to

SELECT Min(tbl_table1.Filed1)
FROM tbl_table1

This select the minimum value but if I add date in as well all records are returned

John
 
John,

To return the minimum for each date:
SELECT Min(Filed1) As MinFiled1, myDate
FROM tbl_table1
GROUP BY myDate

To return the minimum for a specific date:
SELECT Min(Filed1) As MinFiled1, myDate
FROM tbl_table1
GROUP BY myDate
HAVING myDate = [Enter Date]

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Thanks Graham

But I dont think I explained my question properly

The date field is the primary key, hence each date can have only 1 value. i.e. per recor
I can find the minimum value as you suggest, but the issue is I want to know what the date was for the minimum value. Group by myDate wont give me this as it returns all dates

John
 
Back
Top