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

[tvh]

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#];

 

[Duane Hookom]

I expect that one of the joins doesn't reference a primary key field.

 

[Chris]

I was under the impression that it couldn't be done. When I tried, it I
kept getting the message "Recordset is not updateable"

I expect that one of the joins doesn't reference a primary key field.

--
Duane Hookom
MS Access MVP
--

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#];

 

[Duane Hookom]

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

I was under the impression that it couldn't be done. When I tried, it I
kept getting the message "Recordset is not updateable"

I expect that one of the joins doesn't reference a primary key field.

--
Duane Hookom
MS Access MVP
--

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#];