Two tables: Use 2 critiera to look up value

[sudEMS]

There are two tables:



Table 1

- UnitID
- StartDateTime
- EndDateTime
- CallNumber

Table 2

- VehicleID
- DateTimeStamp


What we need is to run a query that will return the following fields

- VehicleID
- DateTimeStamp
- CallNumber

Where:

- UnitID and VehicleID are equal
- DateTimeStamp from Table 2 is greater than the Start time in Table 1 and less than the End time in Table 1.

So without a relationship between the two tables how do we proceed?

[June7]

Assuming the ID fields are number type, try:

SELECT VehicleID, DateTimeStamp, DLookup("CallNumber", "Table1", "UnitID=" & VehicleID & " AND #" & DateTimeStamp & "# BETWEEN StartDateTime AND EndDateTime") As CallNumber;