Date Range matching

[William Demuth]

I have a contract compliance issue that should be simple, but is confusing me.

I have two tables. One is about 160 thousand records, which is a part number, a contract price and a day the price took effect. The items appear several times. Prices go up and down over the course of the data, and items are added and dropped

The second table is a list of dated sales and quotes of those items over the past 14 months.(About 10 thousand lines)

I need to match the items with the contract price that existed on the day the quote or the sale was created, so as to demonstrate we were always at or below the contract price.

Any suggestions????:banghead:

 

[plog]

You are going to have to write some SQL to do this, not just use the query interface.

Here's how:

Using the query interface, create a new query and bring in both your tables.
Link your tables by part number and by the respective date fields.
Bring down all the fields from the Quote table
Bring down the Date field from the price table.
Rename the price date to 'PriceDate'
Make it an aggregate query (click the Sigma/Summation symbol in the ribbon)
Change the 'Group By' to 'Max' under PriceDate
Next, go into SQL view of the query.
In the JOIN clause find where your two date fields are set to each other
Change the '=' to '<='
Save that query as 'PriceQuery_sub1'

Now build another query using PriceQuery_sub1 and your price table. Link them by part number and link PriceQuery_sub1.PriceDate to the date field in your price table. Bring down all the fields you want and that query will produce the results you want.