bradb

Thanks Ocean but I need a bit more detail... I can't for some reason download the example you uploaded, the error I get is: Parse error: parse error, unexpected T_STRING in /home/fmforums/public_html/uploads/1121985808-VIN.fp7 on line 9 So I'm not sure what you mean by a relationship between the first record and the last. How do I do that for each VIN? (there are more than 100,000 of them, each with an ARR, an OUT and any number of other Trans_types stored in separate records) My guess is that you are suggesting that I should define a self-join relationship as follows new_relationship::VIN=VIN & new_relationship::Trans_type=Trans_type & new_relationship::Constant_key=Constant_key where constant_key is the new calculation field with the word "OUT" how does this help me calculate the time elapsed between the record for a Trans_Type=ARR and Trans_type=OUT for the same VIN? (sorry if this is a dumb question, or if it is obvious from the file you uploaded, but I'm new to all of this, including relational databases) Thanks Brad

Hi, I'm new to FM so please bear with me. I have a flat database which contains information about vehicles, specifically when they were recieved and shipped. What I need to find out is the number of days between the date recieved and date shipped. The table looks like this: VIN Trans_Type Date 1 ARR 1/1/2005 1 xxx 1/2/2005 1 OUT 1/3/2005 2 ARR 1/2/2005 ...ect... Where ARR is the code for when the vehicle arrived, OUT is when it shipped and xxx is any number of other Transactions (Trans_type) that occured in between, meaning that there could be upwards of 20 - 30 records between the ARR and OUT. The nice thing is that when the data is sorted by VIN, it places the Trans_Type in cronological order, thus ARR is the first record for each specific VIN and OUT is the last. The database is quite large, over 1 million records, and I'm stuck (since I know squat and this project was dumped on my desk). I run FM pro 7 on Mac OS panther. Any help is greatly appreciated. Brad