Lookup on Date Periods - with another reference

[grey]

I have a list of date periods, which references a particular item. So each item will have one or more periods associated with it. So in the example below, I've concatanated the Item and Period No to give a unique reference for each (UK Dates used in this example):

PERIODS

Item----Period----PeriodRef----Start-----End
A-------1---------A-1----------01/01/09--15/01/09
B-------1---------B-1----------01/01/09--09/01/09
C-------1---------C-1----------05/01/09--12/01/09
C-------2---------C-2----------15/01/09--20/01/09


I then have a list of orders, which I receive from another source, which reference the item no and the date, e.g.:

ORDERS

Item----OrderDate----Quantity
A-------03/01/09-----3
A-------10/01/09-----5
A-------20/01/09-----4
B-------07/01/09-----7
C-------01/01/09-----2
C-------11/01/09-----6
C-------17/01/09-----5
C-------22/01/09-----4


What I want to do is to compare these orders to the periods in the original table to:
- list/summarise orders which were in each period
- list/summarise orders which were outside of a valid period

So in the example above, I'd like to see:

COMPARISON-CALCULATION

Item----OrderDate----Quantity----PeriodRef----InPeriod?
A-------03/01/09-----3-----------A-1----------Y
A-------10/01/09-----5-----------A-1----------Y
A-------20/01/09-----4-----------A-1----------N
B-------07/01/09-----7-----------B-1----------Y
C-------01/01/09-----2-----------C-1----------N
C-------11/01/09-----6-----------C-1----------Y
C-------17/01/09-----5-----------C-2----------Y
C-------22/01/09-----4-----------C-2----------N


In order to produce a final summary as follows, where QntyInPeriod is the quantity of orders which were within a Period for that Item and QntyOutPeriod is the quantity for orders which were not inside one of the periods for that item


PeriodRef----Start-------End-------QntyInPeriod----QntyOutPeriod
A-1----------01/01/09----15/01/09--8---------------4
B-1----------01/01/09----09/01/09--7---------------0
C-1----------05/01/09----12/01/09--6---------------2
C-2----------15/01/09----20/01/09--5---------------4



So my original approach was to :
- concatonate the Item no and Order Date in the ORDERS table
- concatonate the Item no and start date in the PERIODS table
- Lookup the first of these values against the second with a 'TRUE'on the RangeLookup in order to find the nearest value from PERIODS rather than the exact value. This would compare each order to a PeriodRef
- Compare the order date to the start date of the PeriodRef returned to see if it was within the period date or not

This would have worked fine, if it wasn't for the fact that I only want to compare each order against periods related to the item in question. So in the example above, the row from the ORDERS table:

Item----OrderDate----Quantity
C-------01/01/09-----2


Returns B-1 from the PERIODS table.

Does anyone have any ideas as to how I might overcome this?

Thanks in advance for your help!!

[grey]

Sorry about the original formatting on this post - it should hopefully be readable now!