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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tvh - 28 Dec 2004 23:35 GMT

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

Reply to this Message

Duane Hookom - 29 Dec 2004 00:47 GMT

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

Signature

Duane Hookom
MS Access MVP
--

> Access 2003
> When I add a third table to the Query Builder, the query and associated
[quoted text clipped - 23 lines]
> tblClientSiteContacts.tblSiteContactName
> ORDER BY tblWorkOrders.[wotblWorkOrder#];

Reply to this Message

Chris - 30 Dec 2004 17:22 GMT

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.
>
[quoted text clipped - 30 lines]
> > tblClientSiteContacts.tblSiteContactName
> > ORDER BY tblWorkOrders.[wotblWorkOrder#];

Reply to this Message

Duane Hookom - 30 Dec 2004 18:18 GMT

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.

Signature

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"
[quoted text clipped - 35 lines]
>> > tblClientSiteContacts.tblSiteContactName
>> > ORDER BY tblWorkOrders.[wotblWorkOrder#];

Reply to this Message