How do I get the first value on a date/time field

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

Guest

Here' s my situation. I have a table with records like the ones below:

CaseID Date/Time (in one field) Totalworkmin
1 1/1/1900 12:00am 45
1 1/1/1900 2:00pm 6

2 1/1/1900 12:00am 2
2 1/4/1900 4:00pm 22

I want to run a query that finds the first instance for each case ID no
matter if it's on the same day or on separate days. When I use the FIRST
function as part of MSAccess, it doesn't work correctly when I apply it to
the Date/Time field. If I truncate the Date/time Field by using the
DATEVALUE function, it still doesn't work. Has anyone attempted this? How
can I get the results I need?
 
This is a good question. First of all, the first instance depends on how
the records are sorted. It also helps if one of the fields being sorted is
unique, otherwise the "first instance" of a field will actally be different
(i.e., unpredictable) each time you run the query. Do you in fact want the
first instance or "just one" instance?
 
I want the "first instance" per caseid... I should add that there is a fourth
field in the table (query) that need to display. It's name is ProviderNum
and can be the same and/or different between cases. Soo in the example I
gave above the provider num reading down the column is 111, 112, 111,111.

The table I'm querying this from by and large can have one case id per
record however there are times, that the case ID is listed twice (as in two
separate records). This only happens when more than one provider num is
involved.
 
Back
Top