A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Append from 1 table to 2 tables



 
 
Thread Tools Display Modes
  #1  
Old May 21st, 2010, 05:12 PM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default 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  
Old May 24th, 2010, 03:37 AM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default 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  
Old May 24th, 2010, 03:41 AM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default 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  
Old May 24th, 2010, 05:31 AM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default 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  
Old May 24th, 2010, 04:29 PM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:01 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.