Append to two different tables

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

alhotch

I think I know the answer: I want to Append "working" table fields to two
separate tables from a single Append Query. I believe the answer is NO. Can
only Append to one destination table.

So, having said that, what I want to do is this:

I have a csv file that is converted to the "working" table. Call it
tblWorking. This "working" table is deleted (data in the table) after each
Append so there are no records in tblWorking before the csv conversion. The
contents of tblWorking is information pertaining to a person's travel plans
(reservation type data). A portion of the info in tblWorking is to be
appended to tblReservations (name, phone, email, etc.). Other field data in
tblWorking is to be appended to tblReservationDetails (travel date, travel
time, PickUp/Drop Off Location, etc.).

When I append to tblReservations, I get a new record ID (ReservationID) -
tblReservations is linked to tblReservationDetails in a one-to-many
relationship. Once I create a new record in tblReservations and get the new
"ReservationID" autonumber value, I need to process the rest of tblWorking
fields to the associated (linked) tnlReservationDetails table.

What is the best sequence of events (queries) that I should run to "build"
(append) the new record in tblReservations; find the new ReservationID so I
can work with the "many" records aspect of the associated
tblReservationDetails table; and then append the remaining fields from
tblWorking to complete the process ? Keep in mind that I could have several
records in tblReservationDetails (due to round trips, several passengers on
the same reservation, etc.) which are associated with one tblReservation
record.

My FE database form for tblReservations uses a subform for
tblReservationDetails so the main reservation information is presented along
with the detail portion - in the subform.

Thanks for looking at this long question and helping me out.
Al
 
You can append to two tables at once as long as they have the same ID. So if
you have something like "customerid" or "jobbid" in common with all tables,
you can.
 
With Jet, you CAN append new records in more than one table with ONE insert
into: define a query with both tables, have the primary keys present, and
have the JOIN making what you call the 'link' between the two tables. With:


SELECT WhoWhat.id, WhoWhat.who, WhoWhat.what, refWho.id, refWho.refWho,
refWho.thisGuy
FROM refWho INNER JOIN WhoWhat
ON refWho.refWho = WhoWhat.id;



and in the User Interface, trough this query, you can append a new record by
specifying only:
WhoWhat.who, WhoWhat.what, and refWho.thisGuy

(assuming the id are autonumbers). Indeed, not only the autonumbers will be
automatically generated, but also, their transfer will be automatically done
(the generated id from WhoWhat will be copied in refWho.refWho, as per the
join, if the said field is left null the moment before you save the record).
No code required.




Vanderghast, Access MVP
 
Thanks, guys for your help here.

tblReservations autonumber and primary key is ReservationID.
tblReservationDetails autonumber and primary key is ResDetailID.
ReservationID is a foreign key in tblReservationDetails. It is the
one-to-many relationship that "links" tblReservation (one) to
tblReservationDetails (many) that makes this application work.

My concern is that when I do the "append", which creates a new record (in
this case, in BOTH tables), I don't know the new ReservationID and also the
new ReservationDetailsID. Because of the "join", will the new record ID for
ReservationID be "linked" to the new record used by ReservationDetailsID ?
Obviously it is imperative that the final newly appended record(s) show
correct information appended from tblWorking.

True ?
 
You are in a better position than me to see if the query is update-able and
if it transfers the id correctly, or not, with your particular settings. For
'simple' case, it definitively does, as in the case I referred, but it is
also easy to get an not-updateable query when it becomes more complex. Note
that even in cases where it works, you have to specify data such that it
does not contradict itself (such as if the join says: on a.id = b.id), by
leaving the child field null in order to the id from the parent to be
carried over, so you may want to test in the user interface (of data view of
the query) first, to see if it works at all, for your case.


Vanderghast, Access MVP
 
I am now confused as to how to put this query together. My biggest problem is
translating your Filed Names into how they relate to mine. I also can't
figure out once the query is constructed, how do I tell the "Append To"
window which table to use ? I suspect I should use the "parent" table
(tblReservations) but until I get the SQL sequence correct, I can't proceed
to the append process. On other thing, I don't understand your comment about
the "User Interface".

So, here's what I've put together. This is the REAL SQL sequence - I have
put the letters "tbl" in front of my earlier post to show the difference
between "tbl" and "qry". The two tables in my case are Reservations and
ReservationDetails. The tblWorking table contains the fileds I want to append
into BOTH Reservations AND ReservationDetails. Here's my SQL sequence:

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

Keeping it simple, I want to take tblWorking fields FirstName and LastName
(put them together in a field called "FullName" created from LastName,
FirstName) and place them accordingly: Append tblWorking.FirstName AND
tblWoring.LastName to Reservations.FirstName AND Reservations.LastName. THEN,
in the SAME Append, append tblWorking.FullName TO
ReservationDetails.RidersName.

I have trouble following your naming of fields as they compare to mine. Can
you restate your table.field names so they correspond to mine ? And once we
get on the same page, how do I invoke this single query that you refernce as
"... and in the User Interface, trough this query, you can append a new
record by specifying only: ...".

I can sucessfully append data from tblworking.[anyfield] to one of these two
tables. How do I write the single query to get info from tblWorking[anyfield]
into Reservations AND tblWorking[anyotherfield] into ReservationDetails ?

I am really confused at this point. You continued help is MOST appreciated.
 
You insert trough the query, NOT trough one of the tables, since it is the
particular query which holds the gluing logic between the tables. If you
append trough a table, the query won't be considered at all, after all, and
that is not what we want.



Your actual query is probably NOT updateable (because it involves 3 tables,
with a cross join in addition to the inner join) :

---------------
FROM tblWorking,
Reservations INNER JOIN
ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;
---------------

Remove tblWorking, or insert it with its own inner join, but that is already
harder to make three tables working, so, let us start with only two tables.


In the user interface, open the query


--------------
SELECT Reservations.ReservationID, Reservations.FirstName,
Reservations.LastName, ReservationDetails.ReservationID,
ReservationDetails.RidersName
FROM tblWorking, Reservations INNER JOIN ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;
--------------

(I removed tblWorking and the computed expression, a computed expression is
not 'updateable')


***in data view***, and there, manually add a row in the grid, specifying
the fields which are NOT autonumbers (I assume those are ID) and leave the
foreign field null, that is, probably, add values to


Reservations.FirstName,
Reservations.LastName
ReservationDetails.RidersName



And save the record. The Reservations.ReservationID automatically generated
should be carried over to the new record in
ReservationDetails.ReservationID.

If the query is not updatable, if you cannot append a new row, in the grid,
maybe it is because the primary key of ReservationDetails is not included
(probably an autonumber for a field like
ReservationDetails.ReservationDetailsID ), or maybe a relationship between
the two tables is not right.


If your query is not updateable in the User Interface, it won't be
updateable under code. So, be sure you can manually add a row, in the grid,
for that query.

Once it works, manually, try appending using a recordset open on *that
query*, as example, which is somehow easier than using an UPDATE SQL
statement (while keeping the updateability).



Vanderghast, Access MVP
 
I'll need a couple of days to "massage" your helpful instructions. I'm off to
work and then it's evening time.

Thanks again. I think we are getting close ...
 
Well, i'm no farther along with this and I am coming to the conclusion that I
can not APPEND data from tblWorking (source table) to Reservations and
ReservationDetails (TWO target tables).

I create the SQL code that you referenced without using the "computed
expression". This is a SELECT Query. Then you say "*** in data view*** (I
don't know what you mean by "data view") add a row in the grid specifying the
fileds which are NOT autonumbers ... and leave the forgeign field null ....."
Then you say "save the record" At this point, I don't follow what fields you
are referring to when "add a row to the grid" and I am still looking at a
SELECT Query.

When I run this SELECT Query, I get all 350K plus records because I have not
filtered the query to just a new (soon to be appended) record. If I change
the SELECT query to an APPEND query , It takes 30 seconds to process the
query and a message says I am about to append to 690k + rows. Obviously, I
tell Access NOT to append at this stage.

So I started from scratch. I built the query from the SQL point of view
where the INSERT INTO statement references Reservations as the "target"
(quoting Access 2007 Help for Append); ResewrvationID, FirstName,LastName as
the "field1, field2, field3"; SELECT tblworking as the "source"; FirstName,
LastName as "field1, field2"; FROM tblWorking.

Now don't get me wrong, here. I have sucessfully "appended" into the table
Reservations from tblWorking as long as the fields from tblWorking match the
fields in Reservations. But I want to "Append" from tblWorking into two
different (but related - one to many) tables that are working just fine with
over 350K + records "linked" in the aforementioned one-to-many relationship.
The problem is that I must develop the concatenation of tblWorking - fileds
LastName, FirstName - into a third filed named FullName as the value of this
field needs to be added (during the append) to table
ReservationDetails.Fullname. If I can not "compute an expression" in the
append, it looks like I will have to make a separte query - one that does the
concatenation into another field in tblWorking.

I'm going to concentrate on just working with appending data from tblWorking
into the table Reservation. I can dummy up a field from tblWorking that can
insert into Reservations. But I would like to have a corresponding "link" to
ReservationDetails so I can see that the process works to make a new entry in
both tables - Reservations and ReservationDetails.

Iam I making any sense here ? Am I stating my concern correctly ?
 
When I say in "data view" that means NOT in graphical design, NOT in SQL
design, NOT in pivot chart, but in ... data view, using the query as if it
was a table, to see its data. You don't change the SELECT into anything, you
keep it as a it is, as a SELECT, and when you see the data, you have to be
able to append a record, manually, at the bottom of the grid, on the last
row, where the record selector has a * in it and that * changes to a
pencil if you start typing under the fields. If you CANNOT do this, because
the * row is not present, then it is because the query is not updateable
(the joins are not right).

Once you are sure the query is updateable, save it and use it, again, not
changing it to an UPDATE, but using it as if it was a table. But be sure the
query is updateable, otherwise, it would be as if you were using a locked
table.


Alternatively, you can do it with VBA code (and that is probably what you
would see commonly): Open three recordsets (one per table), append a record
in the master table, keep track of autonumber for the newly record you just
appended, store it into a variable, and now, terminate the append in the
master table, then append the new row in each of the other tables using that
value you saved in the variable. Repeat if required for a new (set of) row.
That is definitively more work, but somehow, more commonly seen (probably
because people forget to use queries *as if* they were tables).



Vanderghast, Access MVP
 
Here's my latest APPEND:

INSERT INTO Reservations ( FirstName, LastName )
SELECT tblWorking.FirstName, tblWorking.LastName
FROM tblWorking, Reservations INNER JOIN ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;

This results in the append wanting to append over 190,000 rows. NOT what I
want. And I haven't even considered "appending" to the ReservationDetails
table.
 
NOPE ! The joins must be incorrect. There are NO * or "pencil" when running
the following query:

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

When I look at each table (tblWorking; Reservations; and
ReservationDetails), the last entry on the datasheet is an "asterisk" with
the "...ID" field showing (new).

So, the query is not updateable. Look at the following SQL code:

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

Notice that this query DOES ALLOW for an update (* in last row w/(new) in
"...ID" field. In this query, I eliminated the reference to tblWorking. This
tells me that there is something about tblWorking that is preventing the
"update" capability.

Please feel free to continue your pursuit of this problem as I will
"doggedly" pursue what tblWorking is doing to my code.
 
FROM tblWorking, Reservations INNER JOIN ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;

is not updateable and even if it was, it does not define the glue between
tblWorking and the other tables,what has to be transfered, if any, from
tblWorking to the other tables. What about something like:


FROM

( tblWorking INNER JOIN reservations
ON tblWorking.someID = reservations.someOtherID )

INNER JOIN reservationsDetails
ON oneOfTheFirstTwoTable.thirdID =
reservationsDetails.AgainAnID





ie: make an inner join between tblWorking and one (or the other two)
table(s). Here, it said, on a new record, to use tblWorking.someID as value
for the field someOtherID of reservations, and to use the value thirdID
from table oneOfTheFirstTwoTable (use the real names, not these place
holder) for the unsupplied value of field AgainAnID from table
reservationsDetails.



Vanderghast, Access MVP
 
I beginning to see the light. No relationship (glue) between tblWorking and
the other tables.

We are out of "sync" for ur "posts". Let me work with your latest about an
INNER JOIN on tblWorking and will report back.

Again, MUCH GRAS for your patience on this thread.
 
Here's my latest APPEND:

INSERT INTO Reservations ( FirstName, LastName )
SELECT tblWorking.FirstName, tblWorking.LastName
FROM tblWorking, Reservations INNER JOIN ReservationDetails ON
Reservations.ReservationID = ReservationDetails.ReservationID;

This results in the append wanting to append over 190,000 rows. NOT what I
want. And I haven't even considered "appending" to the ReservationDetails
table.
Click to expand...

DON'T include Reservations or ReservationDetails in the query FROM clause at
all. If you're just inserting names from tblWorking into Reservations, *all*
you need is

INSERT INTO Reservations(FirstName, LastName)
SELECT FirstName, LastName FROM tblWorking;

Since there is nothing (evidently) being inserted into ReservationDetails from
tblWorking, I don't see any point in including it in the query at all. What
information goes into ReservationDetails, and where does that information come
from?
 
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)

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.

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.

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.

What will it take to allow me to make this SINGLE append query work ?
 
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)
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
 
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.

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.

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.

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.

John W. Vinson said:
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)
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...
 
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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