I have a table of data showing where products are stored, the distance and the date it was there. If the stock is moved then the new location is recorded and the date of the movement.
This gives me a table similar to the one below:
My problem is that I need to know, on any given date, where the stock was located (so that I can calculate the distance to the bin)
So how can I get a sale of batch 1 on 01/08/11 to return A035 and a sale of batch 1 on 01/01/12 to return B056?
Currently when I ask it for the Distance it either adds the 3 distances (9+7+15) or returns a Null.
Your assistance will be greatly appreciated as I am tasked to provide reports to measure and drive efficiency.
I look forward to your replies.