Using date ranges

[Rolls]

I encounter frequent situations where I want to join a date field in one
table with a date that lies between a beginning date field and an ending
date field in a second table. I want the query result to include several
other fields from each table such that each record in the first table
matches only one record in the second table.

Examples include:

A daily calculation of the day's closing stock price with a 12-month target
share price picked from another table. The target may change between once
every several years to several times per year.

Using Access to price inventory where a history of multiple price changes
must be retained, in order to charge or credit customers based on an invoice
date that falls into a range of dates that reflect inflationary repricing.

Etc.

I know how to do this with sequential record processing using a loop that
finds the correct record in the second table and then copies all needed
fields and calculations from both tables into a new table. But I wondered
if I had missed something. Can this be accomplished via a join with SQL?
If so, what is a sample SQL statement that does this?

I.e., "Table1.Date1 is 'GE' Table2.Date2 and .LE. Table2.Date3".

 

[Tom Ellison]

Dear Rolls:

If not with a JOIN, then with a filtered cross-product.

Post some SQL or details of the tables and I could work on it for you.

Tom Ellison

 

[Rolls]

Table 1" (close date, share price)

03/24/2006, $58.82
03/31/2006, $59.21

Table 2 (beg date range, end date range, price target)

09/29/2005, 03/26/2006, $55.00
03/27/2006, 12/31/9999, $61.00

Result:

03/24/2006, -$3.82, -6.5%
03/31/2006, +$1.79, +3.0%

In this example a stock has been appreciating. Every Friday the variance
between target price and closing price is calculated in dollars and percent
change. The target price steps up because the company is doing well on
3/27/2006. In a year there are, say, 52 records in table 1 and 3 records in
table 2.

 

[Tom Ellison]

Dear Rolls:

Try this:

SELECT *
FROM [Table 1] T1, [Table 2] T2
WHERE T1.XDate BETWEEN T2.StartDate and T2.EndDate

Not knowing your table names or column names, I just made some up.
Hopefully you can untangle this and give it a try.

Tom Ellison

 

[Rolls]

Thanks, Tom!

I put this criteria in the QBE window under tblPrice:

=[tblTarget]![BegTargetDate] And <=[tblTarget]![EndTargetDate]

and got the appropriate data matched to further develop the desired
calculations.

The SQL is:

SELECT tblPrices.CloseDate, tblPrices.ClosePrice, tblTarget.TargetPrice,
(tblTarget!TargetPrice-tblPrices!ClosePrice) AS PriceChg,
tblTarget!TargetPrice/tblPrices!ClosePrice-1 AS PctChg
FROM tblPrices INNER JOIN tblTarget ON tblPrices.Symbol = tblTarget.Symbol
WHERE (((tblPrices.CloseDate)>=[tblTarget]![BegTargetDate] And
(tblPrices.CloseDate)<=[tblTarget]![EndTargetDate]));

although I didn't use BETWEEN. I'll experiment with it.

The query (above) result displays

CloseDate
ClosePrice
TargetPrice
PriceChg
PctChg

everything I want.

Although I've written Access SQL for many years I always had the idea it
couldn't handle a range of dates and that a join had to match two exact
dates in two tables. So before this I've avoided certain problems involving
date ranges. But not any more!

 

[Tom Ellison]

Dear Rolls:

It works for me too. The BETWEEN is just shorthand. You don't have to
repeat the thing on the left. No real other advantage, except readability.
Whichever you like to see.

Tom Ellison

Thanks, Tom!

I put this criteria in the QBE window under tblPrice:

=[tblTarget]![BegTargetDate] And <=[tblTarget]![EndTargetDate]

and got the appropriate data matched to further develop the desired
calculations.

The SQL is:

SELECT tblPrices.CloseDate, tblPrices.ClosePrice, tblTarget.TargetPrice,
(tblTarget!TargetPrice-tblPrices!ClosePrice) AS PriceChg,
tblTarget!TargetPrice/tblPrices!ClosePrice-1 AS PctChg
FROM tblPrices INNER JOIN tblTarget ON tblPrices.Symbol = tblTarget.Symbol
WHERE (((tblPrices.CloseDate)>=[tblTarget]![BegTargetDate] And
(tblPrices.CloseDate)<=[tblTarget]![EndTargetDate]));

although I didn't use BETWEEN. I'll experiment with it.

The query (above) result displays

CloseDate
ClosePrice
TargetPrice
PriceChg
PctChg

everything I want.

Although I've written Access SQL for many years I always had the idea it
couldn't handle a range of dates and that a join had to match two exact
dates in two tables. So before this I've avoided certain problems
involving date ranges. But not any more!