process table data 2 different ways

[jgerton]

I've got a table "Master" with these fields: flight number, dest1, dest2, dest3, dest4. These fields would be used in a separate table "Market" filled with records of each combination of the destinations.

I want to make fields that auto fill based on contents in dest1-4. They will be fields representing the number of combinations that can be made out of the destinations. Example, dest1 to dest2, dest1 to dest3, dest1 to dest4, dest2 to dest1, dest2 to dest3, dest2 to dest4...and so on.

My goal is to get data from a flight that may have gone from point A-B-C and back to A. We have to record data in a "Market" table that reflects what was on there from A-B, A-C, B-C, B-A, and C-A. Also will have to keep what was on from A-B,B-C,C-A in a separate "Segment" table.

It seems that I should be able to plug data in to one "Master" table and use relationships and field calculations to fill in the other tables. However, flight numbers occasionally get rerouted due to things like weather.

I've been trying several approaches, and referring to the forums for ideas. I just can't seem to get this to work.

[Frenzy]

Are there always only a maximum of 4 destinations per flight nr?

Is it always only a 2-destination combination to assess?

Do you mind running a script after entering the destinations?

[jgerton]

I've changed my whole approach. I'm going to allow for up to 10 stops (just in case) and I have created a field for every possible "Market" pair and "Segment" pair. There are 55 market combinations and just 10 segment combinations.

It's not the prettiest or slickest method, but it gets the job done.

I will continue to try other approaches though, I believe there is a cleaner looking way to do this without so many fields for each record in the table.