Select Distinct Query that is editable

D

David

Hello all,
I have this query. I would like it to return only 1 record that is the
oldest (most days) in the Last_Activity field.
fields are ID (PK autonumber), Login(text), Status(text), Comments(text),
Last_Activity (Date)

'Returns 4 records
SELECT tbl_TDM_Logins.*, tbl_TDM_Logins.Last_Activity
FROM tbl_TDM_Logins
WHERE (((tbl_TDM_Logins.Status)="Available"))
ORDER BY tbl_TDM_Logins.Last_Activity;

I have a field called comments that is different in each record, so group by
doesn't work well.
I have tried to change the query properties to return unique records, or
TOP1, but that query is not editable.
I also tried creating a 2nd query, that only selected the fields LOGIN and
Available, Group by FIRST and then tried to inner join it to the query above,
but that query is also not editable.
How can I limit the records returned from a select query to just 1 and make
it editable?

Any assistance is greatly appreciated.
 
M

MGFoster

David said:
Hello all,
I have this query. I would like it to return only 1 record that is the
oldest (most days) in the Last_Activity field.
fields are ID (PK autonumber), Login(text), Status(text), Comments(text),
Last_Activity (Date)

'Returns 4 records
SELECT tbl_TDM_Logins.*, tbl_TDM_Logins.Last_Activity
FROM tbl_TDM_Logins
WHERE (((tbl_TDM_Logins.Status)="Available"))
ORDER BY tbl_TDM_Logins.Last_Activity;
Click to expand...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Do you mean the column Last_Activity is a date column? If so, you can
get the last date record like this:

SELECT Login, Status, Comments, Last_Activity
FROM tbl_TDM_Logins As L
WHERE Status = 'Available'
AND Last_Activity = (SELECT Max(Last_Activity) FROM tbl_TDM_Logins
WHERE Status = 'Available')

If you don't want the last "Available" record, just remove the WHERE
clause in the subquery, which will find the last date for all records.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxYVooechKqOuFEgEQKIFACfbNZIiuux7kSRrb+80kk3LzexMSoAn0e9
sRs96aFFJ2O9LZUkf17XrTbE
=28CY
-----END PGP SIGNATURE-----
 
Top