If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Append from 1 table to 2 tables
I have a reservation application that I have obtained reservation data from
an email message (daqta from which is entered into the tblWorking table). The email shows travel information either one-way or round trip. One way is referenced by the literal "PRC-PHX". Round trip is referenced by the literal "PRC-PHX-PRC". Depending on the O/W or R/T direction(s), I insert the number one (1) for O/W or the number two (2) for R/T. The form used by the application has a main form (Reservations table) and an embedded subform (ReservationDetails table). The main form has info like Name, Phone, Email, etc. The subform contains the Full Name (Last, First), PickUP Date, Location, Time, Type, Fare, etc. The "Type" field is the one that gets the "1" or "2" - depending on O/W or R/T. One Way (O/W) works fine - Builds a record in the main form and a one line record in the subform. The Reservations table has a one-to-many relationship with the ReservationsDetail table. However, I need to append a second record to the ReservationsDetail table (using the same AutoNumber generated ReservationID record) when I attempt to add the other half of the round trip travel - ie O/W makes one detail record, R/T needs to make two detail records. Below is the current "append" query I use to make the O/W record work. How do I add the capablity to make two records in the same query to reflect travel "out and back" (R/T) ? NSERT INTO qryReservationsBuild03 ( FirstName, LastName, Phone, EmailAddress, RidersName, PickUpDate, PickUpLoc, DropOffLoc, [Time], Type, [Note] ) SELECT tblWorking.FirstName, tblWorking.LastName, tblWorking.Phone, tblWorking.EmailAddress, [tblWorking].[LastName] & ", " & [tblWorking].[FirstName] AS RidersName, ([tblWorking].[SBPUMonth] & "/" & [tblWorking].[SBPUDay] & "/" & [tblWorking].[SBPUYear]) AS PickupDate, tblWorking.SBPULocation, tblWorking.SBDOLocation, tblWorking.SBPUTime, IIf([tblWorking].[TripDirection]="PRC-PHX-PRC","2",IIf([tblWorking].[TripDirection]="PHX-PRC-PHX","2",IIf([tblWorking].[TripDirection]="PRC-PHX","1",IIf([tblWorking].[TripDirection]="PHX-PRC","1")))) AS FirstLeg, tblWorking.Comments FROM tblWorking; |
#2
|
|||
|
|||
Append from 1 table to 2 tables
Let me try to state this request in more simpler terms. tblWorking obtains
values from email information. The fields in this table can have one-way (O/W) information and/or round-trip (R/T) values. In one tblworking record, I can append the O/W information into a NEW record in the table Reservations and table ReservationDetails. Because Reservations has a "one-to-many" relationship with ReservationDetails, a NEW record is created in Reservations AND a NEW record "linked" to Reservations is created in ReservationDetails. So, now I have a record in Reservations "linked" to a corresponding record in ReservationDetails. So far so good - for only putting one record in ReservationDetails that "relates" to the parent (PK) key in Reservations. BUT, if I have R/T values in tblWorking, I NEED to not only create a NEW record in Reservations BUT TWO (2) records in ReservationDetails - one record for, say, outbound travel and one record for return travel. At the time I create the NEW record in Reservations, I do not know the ID (Autonumber) created so I can't refer to it. And I have to "attach" TWO records to ReservationDetails AT THE SAME TIME I create the NEW (primary) record for this passenger. I can create the first (O/W) record in "Details" but how do I create TWO records in "Details" when appending from tblWorking ? I hope I've simplified my question. "alhotch" wrote: I have a reservation application that I have obtained reservation data from an email message (daqta from which is entered into the tblWorking table). The email shows travel information either one-way or round trip. One way is referenced by the literal "PRC-PHX". Round trip is referenced by the literal "PRC-PHX-PRC". Depending on the O/W or R/T direction(s), I insert the number one (1) for O/W or the number two (2) for R/T. The form used by the application has a main form (Reservations table) and an embedded subform (ReservationDetails table). The main form has info like Name, Phone, Email, etc. The subform contains the Full Name (Last, First), PickUP Date, Location, Time, Type, Fare, etc. The "Type" field is the one that gets the "1" or "2" - depending on O/W or R/T. One Way (O/W) works fine - Builds a record in the main form and a one line record in the subform. The Reservations table has a one-to-many relationship with the ReservationsDetail table. However, I need to append a second record to the ReservationsDetail table (using the same AutoNumber generated ReservationID record) when I attempt to add the other half of the round trip travel - ie O/W makes one detail record, R/T needs to make two detail records. Below is the current "append" query I use to make the O/W record work. How do I add the capablity to make two records in the same query to reflect travel "out and back" (R/T) ? NSERT INTO qryReservationsBuild03 ( FirstName, LastName, Phone, EmailAddress, RidersName, PickUpDate, PickUpLoc, DropOffLoc, [Time], Type, [Note] ) SELECT tblWorking.FirstName, tblWorking.LastName, tblWorking.Phone, tblWorking.EmailAddress, [tblWorking].[LastName] & ", " & [tblWorking].[FirstName] AS RidersName, ([tblWorking].[SBPUMonth] & "/" & [tblWorking].[SBPUDay] & "/" & [tblWorking].[SBPUYear]) AS PickupDate, tblWorking.SBPULocation, tblWorking.SBDOLocation, tblWorking.SBPUTime, IIf([tblWorking].[TripDirection]="PRC-PHX-PRC","2",IIf([tblWorking].[TripDirection]="PHX-PRC-PHX","2",IIf([tblWorking].[TripDirection]="PRC-PHX","1",IIf([tblWorking].[TripDirection]="PHX-PRC","1")))) AS FirstLeg, tblWorking.Comments FROM tblWorking; |
#3
|
|||
|
|||
Append from 1 table to 2 tables
Let me see if I can state my question a little clearer.
tblWorking has one record in it (for simplicity). It can contain info for either a O/W trip or a R/T trip. If it is O/W, then my append query below works just fine. It creates a "master" Name; Phone; Email record PLUS a "detail" record for the O/W travel. Now, when I have a R/T consideration, I need to not only create the "master" record BUT two (2) "detail" records - one for the outbound travel and another for the inbound travel. All this with the one append query. Remember, until the append query has run its course, I can not reference the "master" record by its ID because I do not know what it is. Can I do this with just the one existing query or do I have to somehow chop it up so I can produce ONE "master" record and TWO "detail" records from the same single tblWorking record with several queries ? Remember, one "master" and one "detail" record are produced for the O/W scenerio. But how to do the R/T scenerio eludes me. The "master" and "detail" records (thru their respective tables) are in a one-to-many relationship. "alhotch" wrote: I have a reservation application that I have obtained reservation data from an email message (daqta from which is entered into the tblWorking table). The email shows travel information either one-way or round trip. One way is referenced by the literal "PRC-PHX". Round trip is referenced by the literal "PRC-PHX-PRC". Depending on the O/W or R/T direction(s), I insert the number one (1) for O/W or the number two (2) for R/T. The form used by the application has a main form (Reservations table) and an embedded subform (ReservationDetails table). The main form has info like Name, Phone, Email, etc. The subform contains the Full Name (Last, First), PickUP Date, Location, Time, Type, Fare, etc. The "Type" field is the one that gets the "1" or "2" - depending on O/W or R/T. One Way (O/W) works fine - Builds a record in the main form and a one line record in the subform. The Reservations table has a one-to-many relationship with the ReservationsDetail table. However, I need to append a second record to the ReservationsDetail table (using the same AutoNumber generated ReservationID record) when I attempt to add the other half of the round trip travel - ie O/W makes one detail record, R/T needs to make two detail records. Below is the current "append" query I use to make the O/W record work. How do I add the capablity to make two records in the same query to reflect travel "out and back" (R/T) ? NSERT INTO qryReservationsBuild03 ( FirstName, LastName, Phone, EmailAddress, RidersName, PickUpDate, PickUpLoc, DropOffLoc, [Time], Type, [Note] ) SELECT tblWorking.FirstName, tblWorking.LastName, tblWorking.Phone, tblWorking.EmailAddress, [tblWorking].[LastName] & ", " & [tblWorking].[FirstName] AS RidersName, ([tblWorking].[SBPUMonth] & "/" & [tblWorking].[SBPUDay] & "/" & [tblWorking].[SBPUYear]) AS PickupDate, tblWorking.SBPULocation, tblWorking.SBDOLocation, tblWorking.SBPUTime, IIf([tblWorking].[TripDirection]="PRC-PHX-PRC","2",IIf([tblWorking].[TripDirection]="PHX-PRC-PHX","2",IIf([tblWorking].[TripDirection]="PRC-PHX","1",IIf([tblWorking].[TripDirection]="PHX-PRC","1")))) AS FirstLeg, tblWorking.Comments FROM tblWorking; |
#4
|
|||
|
|||
Append from 1 table to 2 tables
alhotch wrote:
I have a reservation application that I have obtained reservation data from an email message (daqta from which is entered into the tblWorking table). The email shows travel information either one-way or round trip. One way is referenced by the literal "PRC-PHX". Round trip is referenced by the literal "PRC-PHX-PRC". Depending on the O/W or R/T direction(s), I insert the number one (1) for O/W or the number two (2) for R/T. The form used by the application has a main form (Reservations table) and an embedded subform (ReservationDetails table). The main form has info like Name, Phone, Email, etc. The subform contains the Full Name (Last, First), PickUP Date, Location, Time, Type, Fare, etc. The "Type" field is the one that gets the "1" or "2" - depending on O/W or R/T. One Way (O/W) works fine - Builds a record in the main form and a one line record in the subform. The Reservations table has a one-to-many relationship with the ReservationsDetail table. However, I need to append a second record to the ReservationsDetail table (using the same AutoNumber generated ReservationID record) when I attempt to add the other half of the round trip travel - ie O/W makes one detail record, R/T needs to make two detail records. Below is the current "append" query I use to make the O/W record work. How do I add the capablity to make two records in the same query to reflect travel "out and back" (R/T) ? just insert another record for the return trip, just reversing the origin and destination. Why do you need to make this so complicated? Keep it simple - it's easier to understand and debug. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201005/1 |
#5
|
|||
|
|||
Append from 1 table to 2 tables
Thanks for the response, Pieter. I understand your answer but what I can't
figure out is how to insert another record by making a single pass at processing the single record in tblWorking. I can not know at the time the records are created just what the record ID is until the process is completed. Ego, I can not add the second record because I don't know what the parent (PK) record number is. I thought maybe the use of a SubQuery might work. The primary query would create the parent record AND the first trip record. Then use an immediate IF to determine if the secord record is required. Would this work ? "PieterLinden via AccessMonster.com" wrote: alhotch wrote: I have a reservation application that I have obtained reservation data from an email message (daqta from which is entered into the tblWorking table). The email shows travel information either one-way or round trip. One way is referenced by the literal "PRC-PHX". Round trip is referenced by the literal "PRC-PHX-PRC". Depending on the O/W or R/T direction(s), I insert the number one (1) for O/W or the number two (2) for R/T. The form used by the application has a main form (Reservations table) and an embedded subform (ReservationDetails table). The main form has info like Name, Phone, Email, etc. The subform contains the Full Name (Last, First), PickUP Date, Location, Time, Type, Fare, etc. The "Type" field is the one that gets the "1" or "2" - depending on O/W or R/T. One Way (O/W) works fine - Builds a record in the main form and a one line record in the subform. The Reservations table has a one-to-many relationship with the ReservationsDetail table. However, I need to append a second record to the ReservationsDetail table (using the same AutoNumber generated ReservationID record) when I attempt to add the other half of the round trip travel - ie O/W makes one detail record, R/T needs to make two detail records. Below is the current "append" query I use to make the O/W record work. How do I add the capablity to make two records in the same query to reflect travel "out and back" (R/T) ? just insert another record for the return trip, just reversing the origin and destination. Why do you need to make this so complicated? Keep it simple - it's easier to understand and debug. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201005/1 . |
Thread Tools | |
Display Modes | |
|
|