Append from 1 table to 2 tables

  • Thread starter Thread starter alhotch
  • Start date Start date
A

alhotch

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;
 
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 said:
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;
Click to expand...
 
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 said:
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;
Click to expand...
 
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 ?
 
Back
Top