B
Bob Vance
Can I sort my query so only the records with the Oldest date is to be shown
J
John W. Vinson
This is not a "sort" - the term "Sort" means to take a set of selected records
and display them in some particular order. You are wanting to display only one
record.
To do so, use a Query with its Top Values property set to 1; sort the records
by descending datefield.
If you want the query to be editable, post back - it's a bit harder.
A
Arvin Meyer [MVP]
Yes, use Ascending order. If you want the oldest records, use the TOP
predicate as in:
SELECT TOP 25 *
FROM MyTable
ORDER BY DateField;
Which will select the oldest 25 records.
predicate as in:
SELECT TOP 25 *
FROM MyTable
ORDER BY DateField;
Which will select the oldest 25 records.
B
Bob Vance
Thanks Arvin, Is it possible only to show one record even if there is 6Arvin Meyer said:
records with the Oldest dates..Regards Bob
SELECT TOP 1 Format([dtDate],"mmmm"", ""yyyy") AS HoldDate, *
FROM tblInvoice_ItMdt
ORDER BY tblInvoice_ItMdt.dtDate DESC;
K
Ken Snell
Assuming you have a primary key in the table:
SELECT Format([dtDate],"mmmm"", ""yyyy") AS HoldDate, *
FROM tblInvoice_ItMdt
WHERE PrimaryKeyField =
(SELECT T.Min(PrimaryKeyField) AS MinPK
FROM tblInvoice_ItMdt AS T
WHERE T.dtDate =
(SELECT Min(U.dtDate) AS Mindt
FROM tblInvoice_ItMdt AS U));
--
Ken Snell
http://www.accessmvp.com/KDSnell/
SELECT Format([dtDate],"mmmm"", ""yyyy") AS HoldDate, *
FROM tblInvoice_ItMdt
WHERE PrimaryKeyField =
(SELECT T.Min(PrimaryKeyField) AS MinPK
FROM tblInvoice_ItMdt AS T
WHERE T.dtDate =
(SELECT Min(U.dtDate) AS Mindt
FROM tblInvoice_ItMdt AS U));
--
Ken Snell
http://www.accessmvp.com/KDSnell/
Bob Vance said:
A
Arvin Meyer [MVP]
Of course that assumes that the Primary Key is an incremental field like an
autonumber. <g>
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
autonumber. <g>
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com
Ken Snell said:
A
Arvin Meyer [MVP]
Your code grabs the newest date, not the oldest. Descending order returns
the most recent date. Typically, since all date field can also store time,
it is advisable to store the time as well. Then you won't be likely to
return 6 records.
As Ken mentioned, you can then get a record from the Primary Key field. But
unless the Primary Key is an autonumber, you cannot guarantee that it will
actually be the first.
K
Ken Snell
Actually it doesn't matter what datatype the primary key field is. The Min
function will work on any data type.
--
Ken Snell
http://www.accessmvp.com/KDSnell/
function will work on any data type.
--
Ken Snell
http://www.accessmvp.com/KDSnell/
Arvin Meyer said:
K
Ken Snell
Arvin Meyer said:
The query statement that I posted first gets all the records with the oldest
date, then selects the record with the minimum primary key value from those
records. So it returns just one record of the ones that have the same,
oldest date.
B
Bob Vance
BRILLIANT 
Thanks Guys
SELECT TOP 1 Format([dtDate],"mmmm"", ""yyyy") AS HoldDate,
tblInvoice_ItMdt.IntermediateID, *
FROM tblInvoice_ItMdt
ORDER BY tblInvoice_ItMdt.dtDate DESC,IntermediateID;
Thanks GuysSELECT TOP 1 Format([dtDate],"mmmm"", ""yyyy") AS HoldDate,
tblInvoice_ItMdt.IntermediateID, *
FROM tblInvoice_ItMdt
ORDER BY tblInvoice_ItMdt.dtDate DESC,IntermediateID;
K
Ken Snell
Yes, good point.
A
Arvin Meyer [MVP]
Ken Snell said:
Yes it will, but if it isn't incremental, like a random autonumber, or a
text datatype, you won't necessarily get the first record.