Please help with my query

[Please Help]

Hello all,

I have two tables: one with data on how much we get charged from our vendors
(Table 1) and one with prices from the industry (Table 2).

Table 1 has Product Code, Transaction Date, Quantity and Amount, and Table 2
has Product Code, Price Type Code, Date and Price per Unit. Product Code is
a key in both tables.

What I want to do is I want to create a query to get the Price per Unit (for
the transactions with the same Product Code) from Table 2 based on the
following criteria:

Get the Price per Unit if the Date (e.g. 07-01-08) from Table 2 is close to
the Transaction Date (e.g. 07-08-08) from Table 2 and Price Type Code is
equal to "B". However, if the Price per Unit is zero for above criteria,
then get the price on closest date with Price Type Code is "A". Even there
are prices that are Date closer to the Transaction Date with Price Type Code
"B", ignore those prices and just pick the latest price with "A".

The query that I need would have the following fields: Product Code,
Transaction Date, Quantity, Price Type Code, Price per unit and Amount.

Thanks.

 

[Please Help]

Sorry for double posting. I thought the first one didn't go through.

Thanks.