Question Temp Table and Matching Dates

[InstructionWhich7142]

I have two tables, one of orders and one of deliveries,

however if an order/delivery is scheduled I cannot compare the two easily as there is no unique reference (badly designed system I'm trying to work around )

we have Order number, and 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 of the type of data attached, basically how do i programmatically match lines like that?:

(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 read the posting guides so i know guess work is not always welcome, but i also dont just want a lump of code, i'd like to work out how to do this, its just a tad beyond me in places

1. my first thought is to create a temp table for every order number and line (would i be able to get around the table number limit if i did this sequencially and passed it back onto a master table?)
   
2. then somehow run something against each temp table to compare the dates, matches the quantities and prints back the delivery date against the schedule date

if thats the right way to go about this then i think i can work out the first bit but i dont want to go down that route if its a total dead end,

the second part i'm stuck on though,

 

[gemma-the-husky]

How can you not have a unique reference - or at least a reference that enables you to match a delivery to a given order

I assume you do have that - so for each order, you actually have a number of deliveries, each for a different date (even that not necessarily) - is that the case? If os, then this is a perfectly normal setup to manage your data - so what are you trying to do, that you cant?

-----------
note that the USUAL set up would be to have orders, linked to order lines, so that an order can have several different products, and the deliveries actually linked to the order lines, rather than the order.


-----------
you mention the SCHEDULE - where do you hold THIS information in your database? Do you really only have 2 tables? Can you post the fields you have in each table, and again expand on what you are trying to do?

 

[InstructionWhich7142]

Edit: Read your signature

Hi Dave,

thanks for the reply, sorry i was not very clear,

there are 3 order tables:

1. Purchase Order Header (this has delivery information etc, key field is Purchase order number)
   
2. Purchase Order Items (this has part numbers and prices etc, key fields are Purchase order number and line number combined, or a field called Job Number (sequential number given to every line of every PO))
   
3. Purchase Order Schedule (this holds the schedules by Purchase order and Line number)

• the other table is the Goods In table, which holds all goods in transactions against Purchase order and Line number

my problem is there is no way to link the 5 deliveries against a line on an order to the 5 schedules they came in against,

all 5 schedule/delivery lines have the same PO number and Line number



I'm trying to assess delivery performance, so comparing the Requested delivery date from the Schedule table against the actual delivery date on the Deliveries table