Minimum (Callout Time)

[AndrewT]

Probably quite an easy one!

I have a table:

amb_id
location
amb1_time
amb2_time
amb3_time

As three ambulances (sometimes) get sent to one call - I wish to create a query which calculates the minimum time an ambulance arrives at the destination.

ie.

amb_id = 143
location = high street
amb1_time = 68 (mins)
amb2_time = 73 (mins)
amb3_time = 59 (mins)

to return:

min_callout_time = 59


Many thanks for your time...

 

[antomack]

Try

SELECT Amb_id, Location, IIf([amb1_time ]<=[amb2_time] And [amb1_time]<=[amb3_time],[amb1_time],IIf([amb2_time]<=[amb1_time] And [amb2_time]<=[uttr3],[amb2_time],IIf([amb3_time]<=[amb1_time] And [amb3_time]<=[amb2_time],[amb3_time]))) AS min_callout_time
FROM YourTableName;

 

[The_Doc_Man]

That table is not normalized, so you have to work too hard to do what should be an easy question.

Assuming you cannot normalize, try a union query approach.

For table:
amb_id
location
amb1_time
amb2_time
amb3_time

Use query:

uqryAMB_TIMES

SELECT amb_id, location, amb1_time as amb_time, 1 as amb_numb from tablename
UNION
SELECT amb_id, location, amb2_time as amb_time, 2 as amb_numb from tablename
UNION
SELECT amb_id, location, amb3_time as amb_time, 3 as amb_numb from tablename

Now select * from uqryAMB_TIMES where amb_id = {whatever} and amb_time = Min(amb_time) ;

Or something kind of like that.

But having that repetitious little data grouping in a single record makes this a harder problem than it should be.

If the table was

Main table:
Amb_ID
Location

Times table:
Amb_ID
Amb_Num
Amb_Time

you could do this more easily.