Append to two different tables

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




"alhotch" wrote in message
...
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

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.

"alhotch" wrote:

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

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.


"vanderghast" wrote:

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




"alhotch" wrote in message
...
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

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.

"vanderghast" wrote:



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

On Thu, 22 Apr 2010 12:39:01 -0700, alhotch
wrote:

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.

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 W. Vinson [MVP]

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 W. Vinson" wrote:

On Thu, 22 Apr 2010 12:39:01 -0700, alhotch
wrote:

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.

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 W. Vinson [MVP]
.

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

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

John W. Vinson [MVP]

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" wrote:

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

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

John W. Vinson [MVP]
.

On Fri, 23 Apr 2010 14:18:01 -0700, alhotch
wrote:

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

John W. Vinson [MVP]