we have two tables, one of orders one of deliveries,

however if an order/delivery is scheduled we cannot compare the two easily as there is no unique reference,

we have Order number, Line number, but not Schedule Line number,

is there a way to look at a list of dates and quantities and compare the two?

i have an excel example, i have attempted to copy it as i cannot upload it:

simple example:

Order:
Line 1 12-May qty 4
Line 2 24-May qty 6
Line 3 03-May qty 5

Delivered:
Line 1 15-May qty 4
Line 2 23-Jun qty 6
Line 3 03-May qty 5

this would be reasonably easy to match as you can link the two tables in a query using "Line"



however this is more like the data we have:

Order:
Line 1 11-Jun qty 3
Line 1 11-Jul qty 3
Line 1 03-Aug qty 4
Line 2 24-May qty 11
Line 3 03-May qty 13

Delivered:
Line 1 15-Jul qty 6
Line 1 02-Aug qty 4
Line 2 11-May qty 11
Line 3 06-May qty 13

how can i programmatically match these lines?

result would look like:
3 items on del line 1 are 34 days late,
3 items on del line 1 are 4 days late
4 items on del line 2 are 1 day early,
11 items on del line 3 are 13 days early
13 items on del line 4 are 3 days late

if a single order line is split over multiple deliveries we would measure against the latest delivery that completes the line and carry the balance over to the next line.


i can guess it would look something like this:



it'd pull up all the dates and quantites against a line and look at the earliest date in both tables, compare the quantites, if the order schedule was less it would mark that date against that line as the delivery date, if it wasn't it would look for the next date in the delivery table and compare that quantity, using that date instead (if it completed the line) then it would move onto the next order line:

ordered 11-jun Qty 3
delivered 15-jul Qty 6
so schedule line 1: ordered 11-jun but delivered 15 jul
now compare schedule line 2:

etc etc

how on earth am i meant to do this in VBA?

help please