I have a perplexing issue that I need some help with,
I have a Historytable, showing historical movements of containers.
Each container will have movement codes attached to its during its life cycle.
These will be
1) GIES or GIED
I need to report the latest transaction applicable to the container and this is queried and uses the "max" function to get the latest transaction date.
The problem becomes an issue, when over a period of time the same container may arrive back in the depot, this creating multiple history lines and movement codes.
the logical sequence is
1) ABCDE123456 can start with either a GIES or GIED movement then
2) ABCDE123456 can go to a IR move the to a
3) ABCDE123456 can go to a OR move then finally
4) ABCDE123456 can go to a GOES move.
All transactions up to line 3 would have to be reported, but if a transaction of GOES exists, then none of the lines are to be reported.
my idea was to incloude a status code in the Historytable that took two values "open" or "closed"
all transactions that are in the system and do not have a GOES move would be classed as "open"
if a transaction is raised for the container that was a GOES move then all the transactions would be classed as closed, (some VB here i think on "lost focus event etc)
Alternatively maintain a seperate transaction table to match on the movement types holding the movement codes of GIES GIED IR OR GOES to query this in addition.
I dont want the solution to be over complex and was hoping I could do this in a query, in conjunction with the Status code mentioned above.
Any assisatnce would be appreciated.