breaking rows (records) in a table into two different tables 1

[bravo6]

I work for a large Travel Management Company. I am creating a data handoff for a customer that has their traveler's ticket information (depart date, time, depart city, arrival city etc.) and return date/time. I have no problem doing this with round trip tickets.

One-way tickets are a problem. Sometimes a "round-trip" is comprised of two-one way tickets. The second one-way ticket departure data is actually the return trip data.

So I need to be able to take the outbound data from the 1st ticket and then move the outbound data from the 2nd ticket into the fields that are for the return data. I need to end up with 1 record per traveler.

The 2 one-way ticket records are in the same table. How can I seperate them into two tables? If I can do that, then I can combine the data from each table as needed.

Any help would be greatly appreciated.

 

[dhookom]

It's possible if you have something in your data that clearly identifies the two tickets are being related.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Make sure if I understand first:

So you have tblA with records that store trip information.
Some of the records are round trips
Some of the records are individual legs.
You like to identify pairs of individual legs representing a round trip.
Remove the pairs from tblA
Add a new round trip into tblA based on the information from the pairs

1) How do you identify a matching pair? Is there a field/s
2) do You need to save the original information for each leg once it was combined into a round trip.

If it was me I think I would like to have a form that shows the possible pairs. The user verifies that they are matching pairs. Then the user clicks a button. An insert query runs to create the new record, deletes the pair records, and if necessary moves the old information to an archive table.

If this is correct you will need to provide the table structure and relevant fields. Then provide which fields map from the old records to the new record. Describe how automated you want this.

 

[bravo6]

Yes, they are realted by last name. Can you tell me how to do it?

Thanks

 

[bravo6]

ok here is a url where I placed a screen shot of a few rows of the table.

http://www.tvlon.com/images/excelscreenshot.jpg

The first row is a round trip ticket and is fine.

The next two rows are two one-way tickets that I want to combine. Row three is really the return portion of the same trip as row 2. I color coded the fields. My ultimate goal is get the data in row 3 to the coresponding color field in row 2. Once accomplished, that will be a complete row and I won't need row 3 any more.

To clarify, this is for reporting purposes only. I am not editing any data in my orginal data source.

Again, if there is a way to seperate row 2 and row 3 into two tables, I can do the rest. The real table may have 100+ rows each day with one way tickets that I want to combine. I do want to automate this. Each outbound one-way ticket will have a coresponding return-trip one-way ticket that can be joined by last name.

thanks for helping

 

[MajP]

Is this an access application or excel?
If this was me and I was doing this in Access.
1) Do a query to return the Second leg information for all records that are second legs along with its first leg ID.
Need RecordKey of second leg
1st leg RecordKey
and the only the pertinent fields from the second leg for input into the first leg

The second leg information can be found something like
qrySecondLeg:

select
leg2.travDate 'colored fields
leg2.arrtime...
leg2.Recordkey as Leg2Recordkey
leg1.Recordkey as Leg1RecordKey
from
flightData as Leg1, FlightData as Leg2
where
Leg2.fromCity1 = "OneWayTicket" and Leg2.fromCity = leg1.fromCity and leg1.lname = leg2.lname


With the above query (once verified) you can then run and insert query because you can join the second leg to the first leg.
qrySecondLeg.Leg1RecordKey = FlightData.Recordkey

then you can run a delete query to delete all records in qrySecondLeg


The above query assumes that no person has multiple one way trips to the same locations.

 

[bravo6]

I appreciate the help very much. This is access I just copied/pasted into excel so I could easily color code and show what the data looks like.

I really don't follow your logic however. There is nothing in the field to tell which leg is the second leg. So I really can't simply select 2nd leg. The oneway ticket that is the return (presumably this is the second leg) will always have a date/time group (column B,C) LATER than the outbound ticket. Again, if the two tickets were in two different tables, it would be easy to select based on which date/time is later.

 

[MajP]

There is nothing in the field to tell which leg is the second leg

You are right I wrote that wrong. Should read:
Leg2.fromCity1 = "OneWayTicket" and Leg2.fromCity = leg1.toCity and leg1.lname = leg2.lname

Yes but in SQL there is a way to determine it. According to your image.

Any record in the table that matches another record in the table where
lastName = lastName
AND fromCity1 = "OneWayTicket"
And its fromCity equals the other record toCity

Is a second leg.

 

[MajP]

Here is a simple demo. Yours would require a few more criteria:

ID	fromCity        toCity	    lname
1	DC	        Baltimore   Smith
2	Baltimore       DC          Smith
3	New York        SanDiego    Jones
4	SanDiego        Newark      Jones
5	San Antonio     Houston     Brown
6	Houston         San Antonio Brown

Sql
SELECT
Leg1.id AS Leg1ID,
Leg2.ID AS Leg2ID,
Leg2.fromCity AS Leg2FromCity,
Leg2.toCity AS Leg2ToCity
FROM
fltData AS Leg1
INNER JOIN
fltData AS Leg2 ON Leg1.lname = Leg2.lname
WHERE
(((Leg2.ID)>[leg1].[id]) AND ((Leg2.fromCity)=[leg1].[toCity]));

Leg1ID	Leg2ID	Leg2FromCity  Leg2ToCity
1	2	Baltimore     DC
3	4	SanDiego      Newark
5	6	Houston	      San Antonio

And you could add both the leg1 other fields as well.

 

[bravo6]

You've gone above and beyond-

With your help I was able to get this done. Thanks for helping me out!

Bravo6