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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Append to two different tables
Sorry for the delay, John.
I have been working on an email/spam issue all weekend and have not yet had time to "get back to my append". Will be working on it tomorrow morning. By the by, when the reservationist in the office "makes" a reservation, using the Reservations form (which adds info to the Reservations table), they key in (and in this order) FirstName, LastName, PhoneNumber. This is the name of the caller making the reservation. Once these three fields are entered, the reservationist moves on to the ReservationDetails subform where the "concatenated" LastName, FirstName is automatically entered into a field named FullName. The next fields are DateTaken, Direction (Northbound/Southbound), PickUP Location, DropOff Location, Time, Type of Trip (R/T, O/W, Child under 12, etc.), Fare, AmtDue, and finally Notes/Comments. Then it's back to the Reservations form (bottom half fo the screen) to calculate the Total Fare, enter any credit card info (if not a cash transaction), and name of the reservationist making the reservation. Here's why I need the FullName in ReservationDetails table. Even though John Smith "makes" the reservation, passengers travelling with him could have LastName, FirstName of Jane Smith (wife), and Sam Smith (child). These three names will be automatically listed on the ReservationDetails subform (FullName), inserted into ReservationDetails.FullName, and when printed, will appear on the driver's travel manifest. The driver needs to know the names of passengers and if they owe any money. The "system" counts these three names as three passengers. It is also quite common for a Jane Smith to "make" the reservation and have a travelling companion by the name of Joe Brown. Jane Smith's name is in the Reservations table. She and Joe Brown's names will be in th ReservationDetails table in the FullName field (separate ReservationDetails records). So you can see I can have several detail records (ReservationDetails table) for each main (Reservations table) record. Sorry for the long explanation as to "Why FullName ?". I'll keep you posted on my progress ... Al "John W. Vinson" wrote: 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] . |
#22
|
|||
|
|||
Append to two different tables
Well John, ACCORDING to Microsoft "Help", i can not append into more than ONE
TABLE using the SQL Statement INSERT INTO. Therefore, the following statment is not valid: INSERT INTO Reservations ( FirstName, LastName ), ReservationDetails (FullName) SELECT tblWorking.FirstName, tblWorking.LastName, [tblWorking].LastName & ", " & [tblWorking].FirstName AS FullName FROM tblWorking, Reservations INNER JOIN ReservationDetails ON Reservations.ReservationID = ReservationDetails.ReservationID; As I stated before, I CAN sucessfully Append (INSERT INTO) in one table (Reservations OR ReservationDetails) but NOT together (append into mulitple tables with one query). So, I tried your "two query" solution. From your previous statement: "Well... try this: create a query joining Reservations to ReservationDetails. Be sure that the query's Unique Records property is set to Yes, and its Recordset Type to Dynaset (Inconsistant Updates). Include the ReservationID field from both tables in the query grid (so Access will be able to increment the autonumber in Reservations and copy that value into the foreign key in ReservationDetails)." Here's what I call qryONE: SELECT Reservations.ReservationID, ReservationDetails.ReservationID, Reservations.FirstName, Reservations.LastName, Reservations.Field22 FROM Reservations INNER JOIN ReservationDetails ON Reservations.ReservationID = ReservationDetails.ReservationID; (Note: Field22 in the Reservations table is an unused field. I use it to get the value from FullName into the table to see if this works) Again, from your previous statement: "Then create an Append query appending from tblWorking into this query: INSERT INTO qryReservations (FirstName, LastName, FullName) SELECT FirstName, LastName, [FirstName] & " " & [LastName] AS Fullname FROM tblWorking; It's a bad idea and again, I don't think the fullname field should even exist, but this should let you do it. -- " Now, here is what I call qryReservations (from your statement): INSERT INTO qryONE ( FirstName, LastName, Field22 ) SELECT FirstName, LastName, [LastName] & ", " & [FirstName] AS Field22 FROM tblWorking; Remember, I use Field22 to show the concatenation of LastName, FirstName. I run the qryReservations and it does indeed populate the Reservations table BUT the AutoNumber ID of Reservations produces a number that is FAR and AWAY from the next number in sequence. I was expecting the next Autonumber to be 335730 but the number produced is 757786. And I DO NOT create any ReservationDetails record. Just an incorrect autonumber entry in Reservations. So I still am not out of the woods. I am more than convinced that I CAN NOT append to more than one table at a time. This means two appends - minimum. One to create the Reservation table record, then another append to put info into the ReservationDetails table. BUT, without creating the record (empty or otherwise) in the ReservatyionDetails table during the FIRST append, all bets are off. What now ? "John 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] . |
#23
|
|||
|
|||
Append to two different tables
UPDATE ! I "Compacted and Repaired" the BE Database and now the Autonumber
sequence is correct. I still don't know how Access is going to create a "detail" record for me using these teo queries. "alhotch" wrote: Well John, ACCORDING to Microsoft "Help", i can not append into more than ONE TABLE using the SQL Statement INSERT INTO. Therefore, the following statment is not valid: INSERT INTO Reservations ( FirstName, LastName ), ReservationDetails (FullName) SELECT tblWorking.FirstName, tblWorking.LastName, [tblWorking].LastName & ", " & [tblWorking].FirstName AS FullName FROM tblWorking, Reservations INNER JOIN ReservationDetails ON Reservations.ReservationID = ReservationDetails.ReservationID; As I stated before, I CAN sucessfully Append (INSERT INTO) in one table (Reservations OR ReservationDetails) but NOT together (append into mulitple tables with one query). So, I tried your "two query" solution. From your previous statement: "Well... try this: create a query joining Reservations to ReservationDetails. Be sure that the query's Unique Records property is set to Yes, and its Recordset Type to Dynaset (Inconsistant Updates). Include the ReservationID field from both tables in the query grid (so Access will be able to increment the autonumber in Reservations and copy that value into the foreign key in ReservationDetails)." Here's what I call qryONE: SELECT Reservations.ReservationID, ReservationDetails.ReservationID, Reservations.FirstName, Reservations.LastName, Reservations.Field22 FROM Reservations INNER JOIN ReservationDetails ON Reservations.ReservationID = ReservationDetails.ReservationID; (Note: Field22 in the Reservations table is an unused field. I use it to get the value from FullName into the table to see if this works) Again, from your previous statement: "Then create an Append query appending from tblWorking into this query: INSERT INTO qryReservations (FirstName, LastName, FullName) SELECT FirstName, LastName, [FirstName] & " " & [LastName] AS Fullname FROM tblWorking; It's a bad idea and again, I don't think the fullname field should even exist, but this should let you do it. -- " Now, here is what I call qryReservations (from your statement): INSERT INTO qryONE ( FirstName, LastName, Field22 ) SELECT FirstName, LastName, [LastName] & ", " & [FirstName] AS Field22 FROM tblWorking; Remember, I use Field22 to show the concatenation of LastName, FirstName. I run the qryReservations and it does indeed populate the Reservations table BUT the AutoNumber ID of Reservations produces a number that is FAR and AWAY from the next number in sequence. I was expecting the next Autonumber to be 335730 but the number produced is 757786. And I DO NOT create any ReservationDetails record. Just an incorrect autonumber entry in Reservations. So I still am not out of the woods. I am more than convinced that I CAN NOT append to more than one table at a time. This means two appends - minimum. One to create the Reservation table record, then another append to put info into the ReservationDetails table. BUT, without creating the record (empty or otherwise) in the ReservatyionDetails table during the FIRST append, all bets are off. What now ? "John 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] . |
#24
|
|||
|
|||
Append to two different tables
WALA !!!! I have achieved SUCCESS !!!
Here are the TWO QUERIES that WORK !!! Query #1 called qryONETest: SELECT DISTINCTROW Reservations.ReservationID, ReservationDetails.ReservationID, Reservations.FirstName, Reservations.LastName, ReservationDetails.RidersName FROM tblWorking, Reservations INNER JOIN ReservationDetails ON Reservations.ReservationID = ReservationDetails.ReservationID; (NOTE: RidersName is the "real name" for aforementioned "FullName") Query #2 is called qryReservationsTest: INSERT INTO qryONETest ( FirstName, LastName, RidersName ) SELECT FirstName, LastName, [LastName] & ", " & [FirstName] AS RidersName FROM tblWorking; I have two records in the tblWorking table and BOTH are appended to the Reservations table. Also created is one record for each Reservation in the ReservationDetails table - using the concatenation of the LastName, FirstName and populating the field "RidersName" (formally FullName) - plus creating an appropriate autonumber in ReservationDetails which allows a realationship to exist between the corresponding record(s) in Reservations table AND ReservationDetails table. Final analysis: Only one table can be appended to using the SQL statement INSERT INTO "table" parameter. However, by using a query as the "table" parameter, as in my first query (qryONETest) in the INSERT INTO statement, I can indeed append into more than one table using only one append query. Microsoft "Help" file on INSERT INTO was not clear on this. My problem was trying to use ONLY ONE QUERY to accomplish the entire task. My sincere thanks again to John Vinson and "Vanderghast" for hanging in there with me. Once again, tenancity has paid off !!! Al "alhotch" wrote: UPDATE ! I "Compacted and Repaired" the BE Database and now the Autonumber sequence is correct. I still don't know how Access is going to create a "detail" record for me using these teo queries. "alhotch" wrote: Well John, ACCORDING to Microsoft "Help", i can not append into more than ONE TABLE using the SQL Statement INSERT INTO. Therefore, the following statment is not valid: INSERT INTO Reservations ( FirstName, LastName ), ReservationDetails (FullName) SELECT tblWorking.FirstName, tblWorking.LastName, [tblWorking].LastName & ", " & [tblWorking].FirstName AS FullName FROM tblWorking, Reservations INNER JOIN ReservationDetails ON Reservations.ReservationID = ReservationDetails.ReservationID; As I stated before, I CAN sucessfully Append (INSERT INTO) in one table (Reservations OR ReservationDetails) but NOT together (append into mulitple tables with one query). So, I tried your "two query" solution. From your previous statement: "Well... try this: create a query joining Reservations to ReservationDetails. Be sure that the query's Unique Records property is set to Yes, and its Recordset Type to Dynaset (Inconsistant Updates). Include the ReservationID field from both tables in the query grid (so Access will be able to increment the autonumber in Reservations and copy that value into the foreign key in ReservationDetails)." Here's what I call qryONE: SELECT Reservations.ReservationID, ReservationDetails.ReservationID, Reservations.FirstName, Reservations.LastName, Reservations.Field22 FROM Reservations INNER JOIN ReservationDetails ON Reservations.ReservationID = ReservationDetails.ReservationID; (Note: Field22 in the Reservations table is an unused field. I use it to get the value from FullName into the table to see if this works) Again, from your previous statement: "Then create an Append query appending from tblWorking into this query: INSERT INTO qryReservations (FirstName, LastName, FullName) SELECT FirstName, LastName, [FirstName] & " " & [LastName] AS Fullname FROM tblWorking; It's a bad idea and again, I don't think the fullname field should even exist, but this should let you do it. -- " Now, here is what I call qryReservations (from your statement): INSERT INTO qryONE ( FirstName, LastName, Field22 ) SELECT FirstName, LastName, [LastName] & ", " & [FirstName] AS Field22 FROM tblWorking; Remember, I use Field22 to show the concatenation of LastName, FirstName. I run the qryReservations and it does indeed populate the Reservations table BUT the AutoNumber ID of Reservations produces a number that is FAR and AWAY from the next number in sequence. I was expecting the next Autonumber to be 335730 but the number produced is 757786. And I DO NOT create any ReservationDetails record. Just an incorrect autonumber entry in Reservations. So I still am not out of the woods. I am more than convinced that I CAN NOT append to more than one table at a time. This means two appends - minimum. One to create the Reservation table record, then another append to put info into the ReservationDetails table. BUT, without creating the record (empty or otherwise) in the ReservatyionDetails table during the FIRST append, all bets are off. What now ? "John 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] . |
Thread Tools | |
Display Modes | |
|
|