Good afternoon.

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
2) IR
3) OR
4) GOES

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

Container ABCDE123456

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.


many thanks..

Trevor Manville

Is there anything else such as a MissionNumber, TripNumber, RoutingNumber which would describe the container leaving the depot? You could look for the last of those then decide if there is a GOES or not.

Failing that, you could do a subquery which would return all records after the date of the last GOES.

Something like this with the right field names:

Select *
From Historytable
Where [transaction date] > DMax("[transaction date]", "Historytable", "[movement codes] = 'GOES'")

You may need to mess with the quotes around GOES to get it to work.

It would really, really be nice if the transaction date field includes the time. Otherwise you could run into problems if the container comes back and leaves the depot on the same day. You could use a => but that might return incorrect records if a container could also have an OR on the same day.

Trevor

I'm a trifle confused. If you're using "max" to find the latest (?most recent?) transaction date, why would it matter if a container has one or one hundred "trails" in history?