If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query Between times
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 |
#2
|
|||
|
|||
Query Between times
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 '================================================= === anna_717717 wrote: 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 |
Thread Tools | |
Display Modes | |
|
|