Append to two different tables

[alhotch]

Sorry for the delay, John.

I have been working on an email/spam issue all weekend and have not yet had
time to "get back to my append". Will be working on it tomorrow morning.

By the by, when the reservationist in the office "makes" a reservation,
using the Reservations form (which adds info to the Reservations table), they
key in (and in this order) FirstName, LastName, PhoneNumber. This is the name
of the caller making the reservation. Once these three fields are entered,
the reservationist moves on to the ReservationDetails subform where the
"concatenated" LastName, FirstName is automatically entered into a field
named FullName. The next fields are DateTaken, Direction
(Northbound/Southbound), PickUP Location, DropOff Location, Time, Type of
Trip (R/T, O/W, Child under 12, etc.), Fare, AmtDue, and finally
Notes/Comments. Then it's back to the Reservations form (bottom half fo the
screen) to calculate the Total Fare, enter any credit card info (if not a
cash transaction), and name of the reservationist making the reservation.

Here's why I need the FullName in ReservationDetails table. Even though John
Smith "makes" the reservation, passengers travelling with him could have
LastName, FirstName of Jane Smith (wife), and Sam Smith (child). These three
names will be automatically listed on the ReservationDetails subform
(FullName), inserted into ReservationDetails.FullName, and when printed, will
appear on the driver's travel manifest. The driver needs to know the names of
passengers and if they owe any money. The "system" counts these three names
as three passengers. It is also quite common for a Jane Smith to "make" the
reservation and have a travelling companion by the name of Joe Brown. Jane
Smith's name is in the Reservations table. She and Joe Brown's names will be
in th ReservationDetails table in the FullName field (separate
ReservationDetails records). So you can see I can have several detail records
(ReservationDetails table) for each main (Reservations table) record.

Sorry for the long explanation as to "Why FullName ?". I'll keep you posted
on my progress ...

Al

Thanks for you prompt response, John.

Fullname IS required as my FE database consists of a frmReservations which
contains FirstName, LastName, PhoneNumber, FareDue, PaymentMethod,
CreditCardNo, TakenBy, and Agent.

In the middle of this FE database form is frmReservationDetails subform
which contains Fullname, TravelDate, Direction(North/South), PULocation,
DOLocation, Time, Type, Fare, Due, and Note fields. Fullname is necessary in
the subform. I guess it could be "computed" at some other point in the
process. I just felt it was easier to do it during the original append.
FullName (LastName, FirstName concatenation) gets plugged into the
ReserfvationDetails table, NEVER in the Reservations table.

Don't confuse data STORAGE with data DISPLAY.

Sure, it's necessary to have fullname on your forms and reports. But it is
*not* necessary to store it in the ReservationDetails table in order to do
so!!!

You can have a textbox (on the mainform or the subform) with a control source

=[FirstName] & " " & [LastName]

to dynamically, automatically, effortlessly derive the fullname.

Reservations.ReservationID is the PK and an Autonumber in table
Reservations. ReservationDetails.ResDetailID is a PK in table
ReservationDetails. Reservations.ReservationID is a FK in ReservationDetails
and is linked from Reservations in a one-to-many relationship. One
Reservation can have Many ReservationDetails. It is the RerservationDetails
fields that populate the subform.

Unless one Reservation can have many ReservationDetails, and those
ReservationDetails have DIFFERENT fullname values (which would be very odd to
my mind), I'll stick by my guns.

So, I can, and have, sucessfully appended selected fields from tblWorking to
table Reservations. But when I do this, I need Access to create an entry in
ReservationDetails that links back to Reservations. With AutoNumber on in
both PKs of Reservations AND ReservationDetails, I need to be sure that even
if I only append fields from tblWorking into table Reservations, a "linked"
entry in table ReservationDetails is created, even if it was empty of any
data. I could always process another query later on to fill in the fields of
table ReservationDetails that are pertinent to it's "parent" record in table
Reservations. I thought I could do this in one append query.

You don't need to include the ResDetailID in the append query, it will
increment automatically.

This "reservation system" has been running for over ten years. I am now
adding the capability to "parse" email reservations into a "csv" file (as
opposed to directly into the DB), and create tblWorking fields from Access
processing of the csv file. A single email contains names, amounts, email
addresses, pickup/dropoff locations, times and dates of travel, etc. From one
email, I have to get the information into both tables - Reservations and
ReservationDetails - from tblWorking.

My suggested query should do that. Try it. If it doesn't please post details
of the manner in which it fails.

 

[alhotch]

Well John, ACCORDING to Microsoft "Help", i can not append into more than ONE
TABLE using the SQL Statement INSERT INTO. Therefore, the following statment
is not valid:

INSERT INTO Reservations ( FirstName, LastName ), ReservationDetails
(FullName)
SELECT tblWorking.FirstName, tblWorking.LastName, [tblWorking].LastName & ",
" & [tblWorking].FirstName AS FullName
FROM tblWorking, Reservations INNER JOIN ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;

As I stated before, I CAN sucessfully Append (INSERT INTO) in one table
(Reservations OR ReservationDetails) but NOT together (append into mulitple
tables with one query).

So, I tried your "two query" solution. From your previous statement:
"Well... try this: create a query joining Reservations to ReservationDetails.
Be sure that the query's Unique Records property is set to Yes, and its
Recordset Type to Dynaset (Inconsistant Updates). Include the ReservationID
field from both tables in the query grid (so Access will be able to increment
the autonumber in Reservations and copy that value into the foreign key in
ReservationDetails)." Here's what I call qryONE:

SELECT Reservations.ReservationID, ReservationDetails.ReservationID,
Reservations.FirstName, Reservations.LastName, Reservations.Field22
FROM Reservations INNER JOIN ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;

(Note: Field22 in the Reservations table is an unused field. I use it to get
the value from FullName into the table to see if this works)

Again, from your previous statement:
"Then create an Append query appending from tblWorking into this query:

INSERT INTO qryReservations (FirstName, LastName, FullName)
SELECT FirstName, LastName, [FirstName] & " " & [LastName] AS Fullname
FROM tblWorking;

It's a bad idea and again, I don't think the fullname field should even exist,
but this should let you do it.
-- "
Now, here is what I call qryReservations (from your statement):

INSERT INTO qryONE ( FirstName, LastName, Field22 )
SELECT FirstName, LastName, [LastName] & ", " & [FirstName] AS Field22
FROM tblWorking;

Remember, I use Field22 to show the concatenation of LastName, FirstName.

I run the qryReservations and it does indeed populate the Reservations table
BUT the AutoNumber ID of Reservations produces a number that is FAR and AWAY
from the next number in sequence. I was expecting the next Autonumber to be
335730 but the number produced is 757786. And I DO NOT create any
ReservationDetails record. Just an incorrect autonumber entry in
Reservations. So I still am not out of the woods.

I am more than convinced that I CAN NOT append to more than one table at a
time. This means two appends - minimum. One to create the Reservation table
record, then another append to put info into the ReservationDetails table.
BUT, without creating the record (empty or otherwise) in the
ReservatyionDetails table during the FIRST append, all bets are off. What now
?

John, thanks for chiming in ...

tblworking is a table containing data derived from processing a csv file.
This table is a "working" (temp) file only and after the data is transferred
from tblWorking to the table Reservations, I will delete the table contents.
Hence, tblWorking does not have to have an AutoNumber data type or PK. There
are 20 + number of fields that I need placed into Reservations but let's deal
with just a few to get this ultimate "append query" to work. The fields in
tblWorking are FirstName, LastName, and a "computed" field (called FullName)
which concatenates from LastName, Firstname (so it looks like Smith, John
when listed in FullName)

FullName should *SIMPLY NOT EXIST*. It's redundant and can be calculated
whenver it's needed unless you have some *really good* reason to store it
redundanly (e.g. if you want to allow for FirstName John, LastName Smith to
have FullName "Chief Wampoag" as a legitimate entry).

The table Reservations contains many fields but does have the filed names of
FirstName and LastName. It is these two fields (for openers) that I want the
same values from tblWorking - just so happens these two fields have the same
names.

Simple append query so far.

Now, table ReservationDetails contains additional information but one of the
fileds in this table is called FullName. It is this field that I intend to
take the "computed" (or concatenated) value from tblWorking and plug it into
the table ReservationDetails.

Again... why? What benefit does storing this redundant data serve?

I thought that I could only "append" from one table to another table. And a
test of this process works as advertised - tblWorking.FirstName appends into
Reservations.FirstName. But recent posts lead me to believe I can append one
table (tblWorking. anyvalue) into one or more table. The tblWorking.anyvalue
goes into table Reservations and the computed value goes into table
ReservationDetails.

I was advised by several posts that if my SELECT query is correct, I should
be able to go to the data form and manually instert records into the
displayed fields (Firstname, LastName). And I have successfully done this,
WITHOUT adding the tblWorking table to the mix. And I was advised that unless
the info in tblWorking is "linked" (through JOINS) properly, there is no way
i wil get this query tro work.

That's where I am at. I can append from tblWorking to Reservations. But
getting to append from tblWorking to Resewrvations AND ReservationDetails has
eluded me.

Well... try this: create a query joining Reservations to ReservationDetails.
Be sure that the query's Unique Records property is set to Yes, and its
Recordset Type to Dynaset (Inconsistant Updates). Include the ReservationID
field from both tables in the query grid (so Access will be able to increment
the autonumber in Reservations and copy that value into the foreign key in
ReservationDetails).

Then create an Append query appending from tblWorking into this query:

INSERT INTO qryReservations (FirstName, LastName, FullName)
SELECT FirstName, LastName, [FirstName] & " " & [LastName] AS Fullname
FROM tblWorking;

It's a bad idea and again, I don't think the fullname field should even exist,
but this should let you do it.

 

[alhotch]

UPDATE ! I "Compacted and Repaired" the BE Database and now the Autonumber
sequence is correct. I still don't know how Access is going to create a
"detail" record for me using these teo queries.

Well John, ACCORDING to Microsoft "Help", i can not append into more than ONE
TABLE using the SQL Statement INSERT INTO. Therefore, the following statment
is not valid:

INSERT INTO Reservations ( FirstName, LastName ), ReservationDetails
(FullName)
SELECT tblWorking.FirstName, tblWorking.LastName, [tblWorking].LastName & ",
" & [tblWorking].FirstName AS FullName
FROM tblWorking, Reservations INNER JOIN ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;

As I stated before, I CAN sucessfully Append (INSERT INTO) in one table
(Reservations OR ReservationDetails) but NOT together (append into mulitple
tables with one query).

So, I tried your "two query" solution. From your previous statement:
"Well... try this: create a query joining Reservations to ReservationDetails.
Be sure that the query's Unique Records property is set to Yes, and its
Recordset Type to Dynaset (Inconsistant Updates). Include the ReservationID
field from both tables in the query grid (so Access will be able to increment
the autonumber in Reservations and copy that value into the foreign key in
ReservationDetails)." Here's what I call qryONE:

SELECT Reservations.ReservationID, ReservationDetails.ReservationID,
Reservations.FirstName, Reservations.LastName, Reservations.Field22
FROM Reservations INNER JOIN ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;

(Note: Field22 in the Reservations table is an unused field. I use it to get
the value from FullName into the table to see if this works)

Again, from your previous statement:
"Then create an Append query appending from tblWorking into this query:

INSERT INTO qryReservations (FirstName, LastName, FullName)
SELECT FirstName, LastName, [FirstName] & " " & [LastName] AS Fullname
FROM tblWorking;

It's a bad idea and again, I don't think the fullname field should even exist,
but this should let you do it.
-- "
Now, here is what I call qryReservations (from your statement):

INSERT INTO qryONE ( FirstName, LastName, Field22 )
SELECT FirstName, LastName, [LastName] & ", " & [FirstName] AS Field22
FROM tblWorking;

Remember, I use Field22 to show the concatenation of LastName, FirstName.

I run the qryReservations and it does indeed populate the Reservations table
BUT the AutoNumber ID of Reservations produces a number that is FAR and AWAY
from the next number in sequence. I was expecting the next Autonumber to be
335730 but the number produced is 757786. And I DO NOT create any
ReservationDetails record. Just an incorrect autonumber entry in
Reservations. So I still am not out of the woods.

I am more than convinced that I CAN NOT append to more than one table at a
time. This means two appends - minimum. One to create the Reservation table
record, then another append to put info into the ReservationDetails table.
BUT, without creating the record (empty or otherwise) in the
ReservatyionDetails table during the FIRST append, all bets are off. What now
?

John, thanks for chiming in ...

tblworking is a table containing data derived from processing a csv file.
This table is a "working" (temp) file only and after the data is transferred
from tblWorking to the table Reservations, I will delete the table contents.
Hence, tblWorking does not have to have an AutoNumber data type or PK. There
are 20 + number of fields that I need placed into Reservations but let's deal
with just a few to get this ultimate "append query" to work. The fields in
tblWorking are FirstName, LastName, and a "computed" field (called FullName)
which concatenates from LastName, Firstname (so it looks like Smith, John
when listed in FullName)

FullName should *SIMPLY NOT EXIST*. It's redundant and can be calculated
whenver it's needed unless you have some *really good* reason to store it
redundanly (e.g. if you want to allow for FirstName John, LastName Smith to
have FullName "Chief Wampoag" as a legitimate entry).

The table Reservations contains many fields but does have the filed names of
FirstName and LastName. It is these two fields (for openers) that I want the
same values from tblWorking - just so happens these two fields have the same
names.

Simple append query so far.

Now, table ReservationDetails contains additional information but one of the
fileds in this table is called FullName. It is this field that I intend to
take the "computed" (or concatenated) value from tblWorking and plug it into
the table ReservationDetails.

Again... why? What benefit does storing this redundant data serve?

I thought that I could only "append" from one table to another table. And a
test of this process works as advertised - tblWorking.FirstName appends into
Reservations.FirstName. But recent posts lead me to believe I can append one
table (tblWorking. anyvalue) into one or more table. The tblWorking.anyvalue
goes into table Reservations and the computed value goes into table
ReservationDetails.

I was advised by several posts that if my SELECT query is correct, I should
be able to go to the data form and manually instert records into the
displayed fields (Firstname, LastName). And I have successfully done this,
WITHOUT adding the tblWorking table to the mix. And I was advised that unless
the info in tblWorking is "linked" (through JOINS) properly, there is no way
i wil get this query tro work.

That's where I am at. I can append from tblWorking to Reservations. But
getting to append from tblWorking to Resewrvations AND ReservationDetails has
eluded me.

Well... try this: create a query joining Reservations to ReservationDetails.
Be sure that the query's Unique Records property is set to Yes, and its
Recordset Type to Dynaset (Inconsistant Updates). Include the ReservationID
field from both tables in the query grid (so Access will be able to increment
the autonumber in Reservations and copy that value into the foreign key in
ReservationDetails).

Then create an Append query appending from tblWorking into this query:

INSERT INTO qryReservations (FirstName, LastName, FullName)
SELECT FirstName, LastName, [FirstName] & " " & [LastName] AS Fullname
FROM tblWorking;

It's a bad idea and again, I don't think the fullname field should even exist,
but this should let you do it.

 

[alhotch]

WALA !!!! I have achieved SUCCESS !!!

Here are the TWO QUERIES that WORK !!!

Query #1 called qryONETest:
SELECT DISTINCTROW Reservations.ReservationID,
ReservationDetails.ReservationID, Reservations.FirstName,
Reservations.LastName, ReservationDetails.RidersName
FROM tblWorking, Reservations INNER JOIN ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;

(NOTE: RidersName is the "real name" for aforementioned "FullName")

Query #2 is called qryReservationsTest:
INSERT INTO qryONETest ( FirstName, LastName, RidersName )
SELECT FirstName, LastName, [LastName] & ", " & [FirstName] AS RidersName
FROM tblWorking;

I have two records in the tblWorking table and BOTH are appended to the
Reservations table. Also created is one record for each Reservation in the
ReservationDetails table - using the concatenation of the LastName, FirstName
and populating the field "RidersName" (formally FullName) - plus creating an
appropriate autonumber in ReservationDetails which allows a realationship to
exist between the corresponding record(s) in Reservations table AND
ReservationDetails table.

Final analysis: Only one table can be appended to using the SQL statement
INSERT INTO "table" parameter. However, by using a query as the "table"
parameter, as in my first query (qryONETest) in the INSERT INTO statement, I
can indeed append into more than one table using only one append query.
Microsoft "Help" file on INSERT INTO was not clear on this.

My problem was trying to use ONLY ONE QUERY to accomplish the entire task.

My sincere thanks again to John Vinson and "Vanderghast" for hanging in
there with me. Once again, tenancity has paid off !!!

Al

UPDATE ! I "Compacted and Repaired" the BE Database and now the Autonumber
sequence is correct. I still don't know how Access is going to create a
"detail" record for me using these teo queries.

Well John, ACCORDING to Microsoft "Help", i can not append into more than ONE
TABLE using the SQL Statement INSERT INTO. Therefore, the following statment
is not valid:

INSERT INTO Reservations ( FirstName, LastName ), ReservationDetails
(FullName)
SELECT tblWorking.FirstName, tblWorking.LastName, [tblWorking].LastName & ",
" & [tblWorking].FirstName AS FullName
FROM tblWorking, Reservations INNER JOIN ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;

As I stated before, I CAN sucessfully Append (INSERT INTO) in one table
(Reservations OR ReservationDetails) but NOT together (append into mulitple
tables with one query).

So, I tried your "two query" solution. From your previous statement:
"Well... try this: create a query joining Reservations to ReservationDetails.
Be sure that the query's Unique Records property is set to Yes, and its
Recordset Type to Dynaset (Inconsistant Updates). Include the ReservationID
field from both tables in the query grid (so Access will be able to increment
the autonumber in Reservations and copy that value into the foreign key in
ReservationDetails)." Here's what I call qryONE:

SELECT Reservations.ReservationID, ReservationDetails.ReservationID,
Reservations.FirstName, Reservations.LastName, Reservations.Field22
FROM Reservations INNER JOIN ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;

(Note: Field22 in the Reservations table is an unused field. I use it to get
the value from FullName into the table to see if this works)

Again, from your previous statement:
"Then create an Append query appending from tblWorking into this query:

INSERT INTO qryReservations (FirstName, LastName, FullName)
SELECT FirstName, LastName, [FirstName] & " " & [LastName] AS Fullname
FROM tblWorking;

It's a bad idea and again, I don't think the fullname field should even exist,
but this should let you do it.
-- "
Now, here is what I call qryReservations (from your statement):

INSERT INTO qryONE ( FirstName, LastName, Field22 )
SELECT FirstName, LastName, [LastName] & ", " & [FirstName] AS Field22
FROM tblWorking;

Remember, I use Field22 to show the concatenation of LastName, FirstName.

I run the qryReservations and it does indeed populate the Reservations table
BUT the AutoNumber ID of Reservations produces a number that is FAR and AWAY
from the next number in sequence. I was expecting the next Autonumber to be
335730 but the number produced is 757786. And I DO NOT create any
ReservationDetails record. Just an incorrect autonumber entry in
Reservations. So I still am not out of the woods.

I am more than convinced that I CAN NOT append to more than one table at a
time. This means two appends - minimum. One to create the Reservation table
record, then another append to put info into the ReservationDetails table.
BUT, without creating the record (empty or otherwise) in the
ReservatyionDetails table during the FIRST append, all bets are off. What now
?

On Fri, 23 Apr 2010 12:05:01 -0700, alhotch

John, thanks for chiming in ...

tblworking is a table containing data derived from processing a csv file.
This table is a "working" (temp) file only and after the data is transferred
from tblWorking to the table Reservations, I will delete the table contents.
Hence, tblWorking does not have to have an AutoNumber data type or PK. There
are 20 + number of fields that I need placed into Reservations but let's deal
with just a few to get this ultimate "append query" to work. The fields in
tblWorking are FirstName, LastName, and a "computed" field (called FullName)
which concatenates from LastName, Firstname (so it looks like Smith, John
when listed in FullName)

FullName should *SIMPLY NOT EXIST*. It's redundant and can be calculated
whenver it's needed unless you have some *really good* reason to store it
redundanly (e.g. if you want to allow for FirstName John, LastName Smith to
have FullName "Chief Wampoag" as a legitimate entry).

The table Reservations contains many fields but does have the filed names of
FirstName and LastName. It is these two fields (for openers) that I want the
same values from tblWorking - just so happens these two fields have the same
names.

Simple append query so far.

Now, table ReservationDetails contains additional information but one of the
fileds in this table is called FullName. It is this field that I intend to
take the "computed" (or concatenated) value from tblWorking and plug it into
the table ReservationDetails.

Again... why? What benefit does storing this redundant data serve?

I thought that I could only "append" from one table to another table. And a
test of this process works as advertised - tblWorking.FirstName appends into
Reservations.FirstName. But recent posts lead me to believe I can append one
table (tblWorking. anyvalue) into one or more table. The tblWorking.anyvalue
goes into table Reservations and the computed value goes into table
ReservationDetails.

I was advised by several posts that if my SELECT query is correct, I should
be able to go to the data form and manually instert records into the
displayed fields (Firstname, LastName). And I have successfully done this,
WITHOUT adding the tblWorking table to the mix. And I was advised that unless
the info in tblWorking is "linked" (through JOINS) properly, there is no way
i wil get this query tro work.

That's where I am at. I can append from tblWorking to Reservations. But
getting to append from tblWorking to Resewrvations AND ReservationDetails has
eluded me.

Well... try this: create a query joining Reservations to ReservationDetails.
Be sure that the query's Unique Records property is set to Yes, and its
Recordset Type to Dynaset (Inconsistant Updates). Include the ReservationID
field from both tables in the query grid (so Access will be able to increment
the autonumber in Reservations and copy that value into the foreign key in
ReservationDetails).

Then create an Append query appending from tblWorking into this query:

INSERT INTO qryReservations (FirstName, LastName, FullName)
SELECT FirstName, LastName, [FirstName] & " " & [LastName] AS Fullname
FROM tblWorking;

It's a bad idea and again, I don't think the fullname field should even exist,
but this should let you do it.