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 ?

"alhotch" wrote:

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

"vanderghast" wrote:

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