Query Between times

[anna_717717]

Hi all, hope somebody can help

I've got two tables. Table 1 has the data against a batch of product, data
about the batch test setup and has the test date and start and end times for
the batch test.
i.e Table 1
BatchID
TestDate
Start time
End time
+test setup data

Table 2 has the the individual results for each product recorded against the
batch, including serial number, test results and test date and test
completion time.
i.e Table 2
BatchID
TestDate
TestCompleteTime
+test result data

I need to be able to identify the batch settings from table 1 for each
product in table 2. How do I create the query so that the records are linked
if the test completion time in table 2 falls between or equal to the start
and end times for the batch
i.e using link criteria:
BatchID = BatchID
TestDate = TestDate
TestCompleteTime = between StartTime and EndTime

Thanks

 

[John Spencer]

SELECT *
FROM Table2 INNER JOIN Table1
ON Table2.BatchID =Table1.BatchID
AND Table1.TestCompleteTime >= Table1.StartTime
AND Table1.TestCompleteTime <= Table1.EndTime

This type of query cannot be built (or displayed) in the query design
view but must be built in the SQL view.

You can build most of the query using the design view and then switch to
SQL view to finish.
Build a query using Design view
-- add both tables
-- add the fields you want to see
-- join the tables on batchId and TestCompleteTime and Starttime
-- Switch to SQL view
-- Modify the ON clause to read like the one above.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================