Append Query - return data for each day within a date range

[capemaybob]

I have a table "Rates" with the following data:

StartDate: 5/15/08 End Date: 6/23/08 Rate: $53
StartDate: 6/24/08 End Date: 7/15/08 Rate: $86
StartDate: 7/16/08 EndDate: 9/19/08 Rate: $99

I have another table "Transactions". I want to create an append query such
that if I supply the StartDate and EndDate, the query will populate the
"Transactons" table with date and rate data from the "Rates" table for each
and every day within the date range. Example: If I supply the StartDate of
6/23/08 and the EndDate of 6/25/08, the query would populate the
"Transactions" table with three entries:

Date: 6/23/08, Rate: 53
Date: 6/24/08, Rate: 86
Date: 6/25/08, Rate: 86

Thanks,
Bob

 

[Dale Fye]

Bob,

Create another table (tbl_Numbers) with a single field intNumbers containing
values from 0 to 9. Create a query (qry_Numbers) from this that looks like:

SELECT Tens.intNumbers * 10 + Ones.intNumbers as intNumbers
FROM tbl_Numbers Tens, tbl_Numbers Ones

This query will now give you the numbers from 0 to 99.

Your new query will look like:

INSERT INTO tbl_Transactions (RateDate, Rate)
SELECT Rates.StartDate + qryNumbers.intNumbers,
Rates.Rate
FROM Rates, qryNumbers
WHERE Rates.StartDate + qryNumbers.intNumbers
BETWEEN Rates.StartDate AND Rates.EndDate

As long as the start and end dates in Rates are not more than 99 days apart,
this will work. If they could be further apart, rewrite qryNumbers to
include another instance of tbl_Numbers aliased as Hundreds. It would look
like:

SELECT Hundreds.intNumbers * 100 + _
Tens.intNumbers * 10 + _
Ones.intNumbers as intNumbers
FROM tbl_Numbers Hundreds, tbl_Numbers Tens, tbl_Numbers Ones

HTH
Dale

 

[Michel Walsh]

SELECT a.itemID, a.transactionDate, b.rate
FROM transactions AS a INNER JOIN rates AS b
ON a.itemID = b.itemID
AND a.transactionDate >= b.startDate
AND a.transactionDate < Nz(b.endDate, 1+Date() )


should do. Note that I assumed the rates are dependant of some ItemID (or
type of service, or whatever). I also assume that if your table of rates
have an endDate set to NULL, that means you don't really know when the price
will end, ie, it is the 'actual' rate, and until further notice, it is still
the valid rate, for today.



Hoping it may help,
Vanderghast, Access MVP