Access Query with values that may have an expiration date

Hi all.

I'm trying to work on an access 2003 query that may have items with expiration date. For example:

Item---UnitofMeasure--ConversionFactor--Expiration
123----CASE-----------50---------------------NULL
456---PACK-----------10--------------------06/20/2008
456--PACK------------20--------------------NULL

The query will have sales history, the date field for sales history would be the invoice date, I would like the query to pull the correct conversion factor for an item and unit of measure based on the invoice date and the expiration date (IF THERE IS ONE).

So, let's say I run the query and I have the following data:

Item---UnitOfMeasure--InvoiceDate
123---CASE-----------05/01/2008
456---PACK-----------05/02/2008
456--PACK------------06/30/2008

I would like the results to be:
Item---UnitOfMeasure-----ConversionFactor--InvoiceDate
123----CASE---------------50--------------------05/01/2008
456---PACK---------------10--------------------05/02/2008
456--PACK----------------20--------------------06/30/2008

As you can see, if there ISN'T an expiration date (NULL) then simply apply the conversion factor that's in the table. But if there is an expiration date, then you have to compare it to the expiration date, if there is an expiration date then all the invoice dates that come before the expiration get the corresponding conversion factor, all invoice dates that come after the expiration date (as long as there is one) would get the conversion factor with the null expiration date for that item (as in the example above for item 456).

Thank you in advance!Start Free Trial