SQL - MIN Date Problem 1

[dcurtis]

I have searched the forums and tried various solutions posted. I am having a problem getting earliest date in my table. The field is called LastAssigned and is formatted as a General Date (since I may have more than one entry on each date). I have tried using

SELECT MIN(LastAssigned) FROM tblLog.

The query returns all the records in the table, not just the "earliest" date. Also, the results are not correct. The query is out of order, meaning a newer date is at the top.

I don't know if it makes a difference but the LastAssign is created by combining 2 fields from a form (Date and Time) input by a user. I can't just use the Now() function since data input may not occur at the time of the assignment.

Clear as mud? Thanks for any help, and I will gladly clarify any points as needed.

----
Access 2002 on a mixed Windows OS network.

 

[lupins46]

' I can't just use the Now() function since data input may not occur at the time of the assignment.'
Well I'm fascinated as to how the date&time of data input can be different to the date& time of data input.

But anyway
'SELECT MIN(LastAssigned) FROM tblLog'
will return one value for the whole table so if you are not getting just one value then this is not the query you are using.

Perhaps you should post the actual query and/or show some sample data and what you want the result to be.

 

[dcurtis]

lupins,

The time of data input may not be the same as the time of assignment, not data entry.

In order to simplfy my posting I omitted that 2 other fields are being returned at the same time. The actual query is

SELECT EmployeeID, EmpDivision, Min(LastAssigned)
FROM tblTowLog
GROUP BY EmployeeID, EmpDivision;

 

[PHV]

If you want the employee having the min date, then try this:
SELECT *
FROM tblTowLog
WHERE LastAssigned = (SELECT Min(LastAssigned) FROM tblLog):

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dcurtis]

Exactly what I was looking for, thanks!