Connect a date in table with the interval between two dates in another table

[Steen Hoffmann]

Hi NG,
I have a database (Access 2000, Win2000) with some measurements carried out
with a chemical substance. The substance has a unique production lot number.
The substance is stable for a couple of months, and then we start using a
new production lot (with a new number).

The database has a table (tbl_Meas) where there is one single measurement at
each date (app. 22 workdays per month). The measurement date is named DateM.

The database has another table (tbl_Prod), containing the production lot
number and two dates: Date1 (the first day this particular lot was used) and
Date2 (the last day this particular lot was used).

Problem:
How do I create a query that connects DateM with the date interval between
Date1 and Date2? The purpose of this query is to show which production lot
number that was used for each of the measurements.
The production lot number has 7-8 characters (letters and numbers) so it
would be convenient to do a query instead of having to enter the production
lot number on each measurement occasion (in a new field in T-1).

Kind regards and thank you
Steen Hoffmann,
Denmark

 

[Albert D. Kallal]

You can certainly add a field to your query that would show the lot number.


select Measurement, MDate,

(select LotNumber from tbl_Prod where Date1 >= MDate and Date2 <= MDate) as
LotN

from tbl_Meas

 

[John Spencer (MVP)]

You can use a NON-EQUI join to do this.

SELECT M.* , P.*
FROM tbl_Meas as M INNER JOIN tbl_Prod as P
ON M.DateM >= P.Date1 AND M.DateM <= P.Date2

You cannot do this in the query grid, but must use the sql window to do the join.

You can build the query in the grid and the join from DateM to each of the
fields and then switch to the SQL view and CHANGE the operators from = to >= and <=.