A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

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

Append to two different tables



 
 
Thread Tools Display Modes
  #1  
Old April 19th, 2010, 05:31 PM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default Append to two different tables

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
  #2  
Old April 19th, 2010, 07:17 PM posted to microsoft.public.access.queries
Golfinray
external usenet poster
 
Posts: 1,597
Default Append to two different tables

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.
--
Milton Purdy
ACCESS
State of Arkansas


"alhotch" wrote:

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

  #3  
Old April 19th, 2010, 07:33 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Append to two different tables

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



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


  #4  
Old April 20th, 2010, 04:06 PM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default Append to two different tables

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 ?

"vanderghast" wrote:

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



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


  #5  
Old April 20th, 2010, 08:06 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Append to two different tables

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

  #6  
Old April 20th, 2010, 10:47 PM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default Append to two different tables

Thanks, again. I'll give it a go tomorrow, and advise.

"vanderghast" wrote:

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

  #7  
Old April 21st, 2010, 05:58 PM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default Append to two different tables

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.



"vanderghast" wrote:

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

  #8  
Old April 21st, 2010, 09:01 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default Append to two different tables

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

  #9  
Old April 21st, 2010, 09:46 PM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default Append to two different tables

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

  #10  
Old April 22nd, 2010, 07:36 PM posted to microsoft.public.access.queries
alhotch
external usenet poster
 
Posts: 58
Default Append to two different tables

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

 




Thread Tools
Display Modes

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

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


All times are GMT +1. The time now is 12:46 PM.


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