Query won't work when I add 3rd table!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access 2003
When I add a third table to the Query Builder, the query and associated form
won't allow edits, additions or deletions. Why? Here's the SQL Statement if
it helps anyone. Thank you in advance!!

SELECT tblClientInformation.tblU2ClientID,
tblClientInformation.citblCompanyName, tblClientInformation.citblContactName,
tblClientInformation.citblContactTitle, tblClientInformation.citblAddress,
tblClientInformation.citblCity, tblClientInformation.citblState,
tblClientInformation.citblZipCode, tblClientInformation.citblWorkPhone,
tblClientInformation.citblExt, tblClientInformation.citblCellPhone,
tblClientInformation.citblFaxNumber, tblClientInformation.citblEmail,
tblWorkOrders.[wotblWorkOrder#], tblWorkOrders.wotblCompanyName,
tblWorkOrders.wotblDate, tblWorkOrders.wotblTime, tblWorkOrders.[wotblJob#],
tblWorkOrders.[wotblPO#], tblWorkOrders.wotblJobDescription,
tblWorkOrders.wotblLocation, tblWorkOrders.wotblServicesRequested,
tblWorkOrders.wotblAdditionalInformation,
tblWorkOrders.wotblInitiallyAssignedTo
FROM (tblClientInformation INNER JOIN tblWorkOrders ON
tblClientInformation.tblU2ClientID = tblWorkOrders.wotblCompanyName) INNER
JOIN tblClientSiteContacts ON tblClientInformation.citblContactName =
tblClientSiteContacts.tblSiteContactName
ORDER BY tblWorkOrders.[wotblWorkOrder#];
 
I was under the impression that it couldn't be done. When I tried, it I
kept getting the message "Recordset is not updateable"
Duane Hookom said:
I expect that one of the joins doesn't reference a primary key field.

--
Duane Hookom
MS Access MVP
--

tvh said:
Access 2003
When I add a third table to the Query Builder, the query and associated
form
won't allow edits, additions or deletions. Why? Here's the SQL Statement
if
it helps anyone. Thank you in advance!!

SELECT tblClientInformation.tblU2ClientID,
tblClientInformation.citblCompanyName,
tblClientInformation.citblContactName,
tblClientInformation.citblContactTitle, tblClientInformation.citblAddress,
tblClientInformation.citblCity, tblClientInformation.citblState,
tblClientInformation.citblZipCode, tblClientInformation.citblWorkPhone,
tblClientInformation.citblExt, tblClientInformation.citblCellPhone,
tblClientInformation.citblFaxNumber, tblClientInformation.citblEmail,
tblWorkOrders.[wotblWorkOrder#], tblWorkOrders.wotblCompanyName,
tblWorkOrders.wotblDate, tblWorkOrders.wotblTime,
tblWorkOrders.[wotblJob#],
tblWorkOrders.[wotblPO#], tblWorkOrders.wotblJobDescription,
tblWorkOrders.wotblLocation, tblWorkOrders.wotblServicesRequested,
tblWorkOrders.wotblAdditionalInformation,
tblWorkOrders.wotblInitiallyAssignedTo
FROM (tblClientInformation INNER JOIN tblWorkOrders ON
tblClientInformation.tblU2ClientID = tblWorkOrders.wotblCompanyName) INNER
JOIN tblClientSiteContacts ON tblClientInformation.citblContactName =
tblClientSiteContacts.tblSiteContactName
ORDER BY tblWorkOrders.[wotblWorkOrder#];
Click to expand...
Click to expand...
 
You should be able to create an updateable query with 3 or more tables. For
instance, open Northwind and use the sql:
SELECT Orders.*, [Order Details].*, Products.*
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID;

The results are updateable.

--
Duane Hookom
MS Access MVP


Chris said:
I was under the impression that it couldn't be done. When I tried, it I
kept getting the message "Recordset is not updateable"
Duane Hookom said:
I expect that one of the joins doesn't reference a primary key field.

--
Duane Hookom
MS Access MVP
--

tvh said:
Access 2003
When I add a third table to the Query Builder, the query and associated
form
won't allow edits, additions or deletions. Why? Here's the SQL Statement
if
it helps anyone. Thank you in advance!!

SELECT tblClientInformation.tblU2ClientID,
tblClientInformation.citblCompanyName,
tblClientInformation.citblContactName,
tblClientInformation.citblContactTitle, tblClientInformation.citblAddress,
tblClientInformation.citblCity, tblClientInformation.citblState,
tblClientInformation.citblZipCode, tblClientInformation.citblWorkPhone,
tblClientInformation.citblExt, tblClientInformation.citblCellPhone,
tblClientInformation.citblFaxNumber, tblClientInformation.citblEmail,
tblWorkOrders.[wotblWorkOrder#], tblWorkOrders.wotblCompanyName,
tblWorkOrders.wotblDate, tblWorkOrders.wotblTime,
tblWorkOrders.[wotblJob#],
tblWorkOrders.[wotblPO#], tblWorkOrders.wotblJobDescription,
tblWorkOrders.wotblLocation, tblWorkOrders.wotblServicesRequested,
tblWorkOrders.wotblAdditionalInformation,
tblWorkOrders.wotblInitiallyAssignedTo
FROM (tblClientInformation INNER JOIN tblWorkOrders ON
tblClientInformation.tblU2ClientID = tblWorkOrders.wotblCompanyName) INNER
JOIN tblClientSiteContacts ON tblClientInformation.citblContactName =
tblClientSiteContacts.tblSiteContactName
ORDER BY tblWorkOrders.[wotblWorkOrder#];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top