Look-Up or Calculation ??

[RickK]

Need help or suggestions for this task:

Two databases with a relationship. db #1 is our shipments database, db #2 is a informational database. The problem is getting the final ship date from the "shipments" db into the "info" db. The shipments database might have 1-10 shipments (individual records) for a specific job number before the shipment is marked as completed. When marked as complete a date field is filled in with the corresponding final ship date (the relationship is based upon a job number). The final shipment is not always the last shipment.

I've used "look-up", I've tried a calucation, but the trouble I'm have is getting it to look through all records with that job number and find the one with the "completed" ship date

Maybe I need to run a loop script?

Any ideas

thx

[slstrother]

One way to do this is to use the Max function. In your Info db, create a calc field:

Final_Shipment= Max(shipments::ship_date)

This assumes that you have a relationship from the Info db to the Shipments db based on the job number (sorry, your post wasn't clear). The date field you use in the shipments db can be the one used on each shipment or another date field that is used for the final shipment.

Hope this helps

[RickK]

I'll give this a try.. thanks