Access Query

  • Thread starter Thread starter Jeff S
  • Start date Start date
J

Jeff S

Hi,

I have two fields - a Delivery Number and a Timeaslot,
when that delivery will be delivered, as shown. The
underscore and number indicates a line on that delivery.,
ie, _10 is line 10 etc.

DO_Item Timeslot
81579428_10 5:30:00 AM
81579428_30 7:30:00 AM
81579428_40 7:30:00 AM
81579428_50 7:30:00 AM
81579428_60 6:00:00 AM
81579428_70 10:30:00 AM
81579428_80 8:30:00 AM
81579428_90 6:00:00 AM

I am trying write a query that only displays the last time
that the delivery will be actioned, in the scenario above
it would be 10:30am.
 
Well, it looks as if you are storing two p;ieces of information in one field.
So your first task is to break out the Delivery Number from the Line number.
Since I don't know if your DO_Item is alway 8 numbers, an underscore, and 2
numbers or some other format, I chose to do it by using the position of the underscore.

SELECT DO_Item, TimeSlot
FROM YourTable as T
WHERE T.TimeSlot =
(SELECT Max(Tmp.TimeSlot)
FROM YourTable as Tmp
WHERE Left(Tmp.Do_Item,Instr(Tmp.Do_Item,"_")-1) =
Left(T.Do_Item,Instr(T.Do_Item,"_")-1))
 
Back
Top