Running A Cross Tab Querry and Selecting a Price Between Two Date Ranges

[PharmGuyFl]

Hello everyone. Thanks you for your consideration for viewing this question. I'm semi familiar with access and I'm trying to run a cross tab query using an existing database along with an imported excel data table which contains a pricing logic.
The database (Table1) contains the fields... Contract Reference, Transaction #, Transaction Date, NDC codes, Quantity Sold, and Wholesale Price along with other things that are not important to the query.

The imported data table (Table2) contains the fields… Contract Reference, Beginning Date for Price, End Date for Price, NDC codes, Item Description, and Contract Price.





I want to be able to display all the fields mentioned from (Table 1) and then the Contract Price from (Table 2) using some method beyond my current abilities. The problem is I need to use the Transaction Date from (Table 1) and have it select the correct price based on Begin Date and End Date(Table 2).

ContractRef	PriceStartDate	PriceEndDate	NDC	Item Description	Contract Price
ABC11	6/1/2011	9/29/2011	123	Widget 1	10.13
ABC11	9/30/2011	9/30/2011	123	Widget 1	9.74
ABC11	10/1/2011	11/14/2011	123	Widget 1	9.74
ZXY09	1/1/2009	7/31/2009	912	Widget 2	11.65
ZXY09	8/1/2009	12/31/2009	912	Widget 2	11.97
ZXY09	1/1/2010	7/18/2010	912	Widget 2	11.97
ZXY09	8/1/2009	12/31/2009	789	Widget 3	8.97
ZXY09	1/1/2010	7/18/2010	789	Widget 3	9

An example of (Table2) is displayed above. Notice the price changes. I currently am running a query linking only Contract Ref and NDC Codes from the two tables. I want my query to pull the appropriate contract price based upon PriceStartDate and PriceEndDate and display it with table 1. This will hopefully display the correct price for the time period. Can someone please help me improve my method?

[pbaldy]

I believe what you want is a non-equi join. Generically:

SELECT...
FROM DataTable INNER JOIN PriceTable ON DataTable.DateField >= PriceTable.PriceStartDate AND DataTable.DateField <= PriceTable.PriceEndDate

Note this join can't be represented in design view, so has to be done in SQL view.

[June7]

One way is to use DLookup:
SELECT *, DLookup("[Contract Price]", "Table2", "#" & [Transaction Date] & "# BETWEEN PriceStartDate AND PriceEndDate") As Price FROM Table1;

Another is a query that has both tables without a JOIN clause. This will cause every record in each table to join with every record in other table. Then filter criteria under Contract Price field: BETWEEN PriceStartDate AND PriceEndDate

Advise not to use spaces, special characters/punctuation (# $ @ & | / ? ( ) [ ] { } - underscore is exception) in names nor reserved words as names. If used must enclose in [].

EDIT: didn't see Paul's post. Interesting query, never tried that one.

[PharmGuyFl]

Thanks for the reply PBALDY. The non-equal join works perfectly. It took me a little time too get it working, as I'm not very familiar with working in the SQL view, but so far so good. Btw... i just realized in my table I posted it didnt' display price field. Sorry about not making the question clear if I did not.

[pbaldy]

Glad it worked for you. It's one of the lesser known techniques, but quite handy.