Another Dlookup question

[ngan]

I have a table (tblAddress) that lists pickup addresses for taxis and a
subtable that lists the number of times a client calls in (tblCalls) for that
one pickup address.

I also have a table (tblBackup) that lists any vehicles that was sent as a
backup (in case the original taxi is late). It's linked to the tblAddress.

tblAddress
AddieID, PUAddress, PUCity...
tblCalls
CallID, * CallNo (1, 2, or 3), CallTime, disposition...
tblBackup
BackupID, AddieID, backupprovider, *WhichCall, DispatchTime, Arrivetime...

*CallNo refers to if it's the first, second or final call regarding that
address.
*WhichCall refers to the CallID of the call that initiated the backup vehicle

In a report, I want to get the the length of time between the calltime of
the First call and the dispatchtime of the backup. If the vehicle was
dispatched on the second call, I need the calculation to be the different of
the CallTime of the First Call and the dispatchtime of the backup.

Here's what I have to do in my query:
LOTFirstCallDispatch: DateDiff("n",DLookUp("CallTime","tblCall","CallNo = 1
and AddieID= " & [tblAddress].[AddieID]),[DispatchTime])

If I don't do a dlookup, it will grab the call time of whichever call the
backup was sent (in this case, the 2nd call).

Is there an easier way of getting the call time from another Call record
without using dlookup?

As with any domain aggregate function, it takes a long time to run the
query. I just wondered if there were other options to use.

 

[sanfu]

I have a table (tblAddress) that lists pickup addresses for taxis and a
subtable that lists the number of times a client calls in (tblCalls) for that
one pickup address.

I also have a table (tblBackup) that lists any vehicles that was sent as a
backup (in case the original taxi is late). It's linked to the tblAddress.

tblAddress
AddieID, PUAddress, PUCity...
tblCalls
CallID, * CallNo (1, 2, or 3), CallTime, disposition...
tblBackup
BackupID, AddieID, backupprovider, *WhichCall, DispatchTime, Arrivetime...

*CallNo refers to if it's the first, second or final call regarding that
address.
*WhichCall refers to the CallID of the call that initiated the backup vehicle

In a report, I want to get the the length of time between the calltime of
the First call and the dispatchtime of the backup. If the vehicle was
dispatched on the second call, I need the calculation to be the different of
the CallTime of the First Call and the dispatchtime of the backup.

Here's what I have to do in my query:
LOTFirstCallDispatch: DateDiff("n",DLookUp("CallTime","tblCall","CallNo = 1
and AddieID= " & [tblAddress].[AddieID]),[DispatchTime])

If I don't do a dlookup, it will grab the call time of whichever call the
backup was sent (in this case, the 2nd call).

Is there an easier way of getting the call time from another Call record
without using dlookup?

As with any domain aggregate function, it takes a long time to run the
query. I just wondered if there were other options to use.

**This is untested!!**

I don't quite understand the relationships between the tables, but if you
*always* want call number one from tblCalls, create a query based on
tblCalls. Maybe name it "qryFirstCalls". Add all of the fields (or the ones
you need) and in the criteria row for the field "CallNo", enter 1. This gives
you only records that are first calls.

I'm guessing that the three tables are linked by "AddieID".

In your query, use tblAddress, tblBackup and the query (qryFirstCalls?). In
the grid, drag down fields you need from the tables and query.

Then your expression might look like:

LOTFirstCallDispatch: DateDiff("n",[CallTime],[DispatchTime])


Remember, **This is untested!!**. But maybe it will help a little...

HTH