I have a "Ship Movement" table that has individual records for each ship. There are multiple ships and they arrive at different times; some on the same day and other a day or two later. Some ships have two people departing at a certain location at exactly the same time, and others have just one. These persons are captured in the same record as fields "person#1" and "person#2". The ship is identified as "ship_name" and there is a primary key called "__kp_booking".
I need to separate these persons into individual records and each having the same "ship_name", "arrival_date", "arrival_time", "arrival_location", "departure_date", "departure_time", "departure_location". I am not sure how to do this.
I am assuming that I will need to have a separate table where the "separated" records are kept. I need some help in writing a script that will do this. It will need to create a new record IF a second person is getting off the same ship or just copy the original record IF it is only one person. In either case, the "person#1" "person#2" fields will have to be converted into a singular "person" field.
From these results I will then need to create a report that shows whether these people will be in for breakfast, lunch or dinner. It will depend on the arrival and departure times of each individual person. More on that later....
Any help would be appreciated.