Query is not updatable -

  • Thread starter Doug Johnson via AccessMonster.com
  • Start date
D

Doug Johnson via AccessMonster.com

Please help. I've created a query which is pulling data from 11 tables. The
query is not updatable. I've spent hours reading other postings re: this
problem, but cannot find a solution. A few Access classes and books have not
been helpful. Ya, I'm pulling my hair out! If I create a query with just 2
of the 11 tables, then it is updatable. When I add the 3rd, it becomes NOT
updatable. Are there step-by-step procedures available to follow when
creating tables and an updatable query?

Many thanks.
-Doug

Here's the SQL - I do not yet know SQL. Also, I didn't delete the "Make
Table" text from the table names that I created from the original Access
database which consisted of just 1 Table with all the same fields (flat file).


SELECT [T - Make Table - Person].ID AS [T - Make Table - Person_ID], [T -
Make Table - Dates].DateOfInquiry, [T - Make Table - Dates].[Last Input /
Updated], [T - Make Table - Dates].DateEntered, [T - Make Table - Dates].
DateUpdated, [T - Make Table - Dates].[Reviewed 08-11-05], [T - Make Table -
Company].CompanyName, [T - Make Table - Company].DBA, [T - Make Table -
Company].StreetNumber, [T - Make Table - Company].StreetName, [T - Make Table
- Company].[Street Address 1], [T - Make Table - Company].[Street Address 2],
[T - Make Table - Company].Suite, [T - Make Table - Company].POBoxNumber, [T -
Make Table - Company].City, [T - Make Table - Company].State, [T - Make Table
- Company].PostalCode, [T - Make Table - Company].NoOfYearsInBusiness, [T -
Make Table - Company].CurrentLocations, [T - Make Table - Person].Prefix, [T -
Make Table - Person].FirstName, [T - Make Table - Person].LastName, [T - Make
Table - Person].Title, [T - Make Table - Categories].[Main Category], [T -
Make Table - Categories].[Sub-Category], [T - Make Table - Categories].
[Business Category (OAK website)], [T - Make Table - Categories].[Drawer &
Folder for Source Docs], [T - Make Table - Categories].Use, [T - Make Table -
Categories].Comment, [T - Make Table - Categories].Description, [T - Make
Table - Categories].[OAK website form], [T - Make Table - Categories].
[Concession Category (Round 1)], [T - Make Table - Categories].[Main
Categories], [T - Make Table - Phone].HomePhone, [T - Make Table - Phone].
WorkPhone, [T - Make Table - Phone].Extension, [T - Make Table - Phone].
MobilePhone, [T - Make Table - Phone].FaxNumber, [T - Make Table - Phone].
EmailAddress, [T - Make Table - Phone].EmailAddressAlt, [T- Make Table -
Source].ReferredBy, [T- Make Table - Source].[How did you find out about this
website?], [T- Make Table - Source].[Source document], [T- Make Table -
Duplicate Record].[Duplicate Record], [T - Make Table - T2X Round 1].[In T2
Expansion 1st Round table?], [T - Make Table - SRD RFP].[RFQ name], [T - Make
Table - SRD RFP].MBEStatus, [T - Make Table - SRD RFP].DBEStatus, [T - Make
Table - SRD RFP].WBEStatus, [T - Make Table - SRD RFP].LBAStatus, [T - Make
Table - SRD RFP].[RFPName&Date], [T - Make Table - SRD RFP].[RFP Status], [T -
Make Table - SRD RFP].DateRFQmailed, [T - Make Table - Returned Mail].
MailSubject, [T - Make Table - Returned Mail].MailDateSent, [T - Make Table -
Returned Mail].MailDateReturned, [T - Make Table - Returned Mail].[Returned
Mails], [T - Make Table - Returned Mail].ReturnedUndeliverable, [T - Make
Table - Returned Mail].EmailDateSent, [T - Make Table - Returned Mail].
EmailDateReturned, [T - Make Table - North Field].Northfield, [T - Make Table
- Phone].BadEmailAddress, [T - Make Table - Phone].DateEmailFailed, [T - Make
Table - SRD RFP].[Include in RFPfbngd2006 Mailing List], [T - Make Table -
SRD RFP].[Include in EOISwanWy2005 Mailing List]
FROM (((((((([T - Make Table - Person] INNER JOIN ([T - Make Table -
Categories] INNER JOIN [T - Make Table - North Field] ON [T - Make Table -
Categories].ID = [T - Make Table - North Field].ID) ON [T - Make Table -
Person].ID = [T - Make Table - Categories].ID) INNER JOIN [T - Make Table -
Company] ON [T - Make Table - Person].ID = [T - Make Table - Company].ID)
INNER JOIN [T - Make Table - Dates] ON [T - Make Table - Person].ID = [T -
Make Table - Dates].ID) INNER JOIN [T - Make Table - Phone] ON [T - Make
Table - Person].ID = [T - Make Table - Phone].ID) INNER JOIN [T - Make Table -
Returned Mail] ON [T - Make Table - Person].ID = [T - Make Table - Returned
Mail].ID) INNER JOIN [T - Make Table - SRD RFP] ON [T - Make Table - Person].
ID = [T - Make Table - SRD RFP].ID) INNER JOIN [T - Make Table - T2X Round 1]
ON [T - Make Table - Person].ID = [T - Make Table - T2X Round 1].ID) INNER
JOIN [T- Make Table - Duplicate Record] ON [T - Make Table - Person].ID = [T-
Make Table - Duplicate Record].ID) INNER JOIN [T- Make Table - Source] ON [T -
Make Table - Person].ID = [T- Make Table - Source].ID;
 
J

jleckrone

Make sure that the tables you are joining all have primary keys
defined. Usually when the problem you have described happens to me, I
have forgotten to define my key for one of my tables.
 
D

Douglas J. Steele

It's rare that you'll be able to have an 11 table join updatable.

Take a look at
http://msdn.microsoft.com/library/en-us/off2000/html/acconDeterminingWhenCanUpdateDataQueryS.asp

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug Johnson via AccessMonster.com said:
Please help. I've created a query which is pulling data from 11 tables.
The
query is not updatable. I've spent hours reading other postings re: this
problem, but cannot find a solution. A few Access classes and books have
not
been helpful. Ya, I'm pulling my hair out! If I create a query with just
2
of the 11 tables, then it is updatable. When I add the 3rd, it becomes
NOT
updatable. Are there step-by-step procedures available to follow when
creating tables and an updatable query?

Many thanks.
-Doug

Here's the SQL - I do not yet know SQL. Also, I didn't delete the "Make
Table" text from the table names that I created from the original Access
database which consisted of just 1 Table with all the same fields (flat
file).


SELECT [T - Make Table - Person].ID AS [T - Make Table - Person_ID], [T -
Make Table - Dates].DateOfInquiry, [T - Make Table - Dates].[Last Input /
Updated], [T - Make Table - Dates].DateEntered, [T - Make Table - Dates].
DateUpdated, [T - Make Table - Dates].[Reviewed 08-11-05], [T - Make
Table -
Company].CompanyName, [T - Make Table - Company].DBA, [T - Make Table -
Company].StreetNumber, [T - Make Table - Company].StreetName, [T - Make
Table
- Company].[Street Address 1], [T - Make Table - Company].[Street Address
2],
[T - Make Table - Company].Suite, [T - Make Table - Company].POBoxNumber,
[T -
Make Table - Company].City, [T - Make Table - Company].State, [T - Make
Table
- Company].PostalCode, [T - Make Table - Company].NoOfYearsInBusiness,
[T -
Make Table - Company].CurrentLocations, [T - Make Table - Person].Prefix,
[T -
Make Table - Person].FirstName, [T - Make Table - Person].LastName, [T -
Make
Table - Person].Title, [T - Make Table - Categories].[Main Category], [T -
Make Table - Categories].[Sub-Category], [T - Make Table - Categories].
[Business Category (OAK website)], [T - Make Table - Categories].[Drawer &
Folder for Source Docs], [T - Make Table - Categories].Use, [T - Make
Table -
Categories].Comment, [T - Make Table - Categories].Description, [T - Make
Table - Categories].[OAK website form], [T - Make Table - Categories].
[Concession Category (Round 1)], [T - Make Table - Categories].[Main
Categories], [T - Make Table - Phone].HomePhone, [T - Make Table - Phone].
WorkPhone, [T - Make Table - Phone].Extension, [T - Make Table - Phone].
MobilePhone, [T - Make Table - Phone].FaxNumber, [T - Make Table - Phone].
EmailAddress, [T - Make Table - Phone].EmailAddressAlt, [T- Make Table -
Source].ReferredBy, [T- Make Table - Source].[How did you find out about
this
website?], [T- Make Table - Source].[Source document], [T- Make Table -
Duplicate Record].[Duplicate Record], [T - Make Table - T2X Round 1].[In
T2
Expansion 1st Round table?], [T - Make Table - SRD RFP].[RFQ name], [T -
Make
Table - SRD RFP].MBEStatus, [T - Make Table - SRD RFP].DBEStatus, [T -
Make
Table - SRD RFP].WBEStatus, [T - Make Table - SRD RFP].LBAStatus, [T -
Make
Table - SRD RFP].[RFPName&Date], [T - Make Table - SRD RFP].[RFP Status],
[T -
Make Table - SRD RFP].DateRFQmailed, [T - Make Table - Returned Mail].
MailSubject, [T - Make Table - Returned Mail].MailDateSent, [T - Make
Table -
Returned Mail].MailDateReturned, [T - Make Table - Returned
Mail].[Returned
Mails], [T - Make Table - Returned Mail].ReturnedUndeliverable, [T - Make
Table - Returned Mail].EmailDateSent, [T - Make Table - Returned Mail].
EmailDateReturned, [T - Make Table - North Field].Northfield, [T - Make
Table
- Phone].BadEmailAddress, [T - Make Table - Phone].DateEmailFailed, [T -
Make
Table - SRD RFP].[Include in RFPfbngd2006 Mailing List], [T - Make
Table -
SRD RFP].[Include in EOISwanWy2005 Mailing List]
FROM (((((((([T - Make Table - Person] INNER JOIN ([T - Make Table -
Categories] INNER JOIN [T - Make Table - North Field] ON [T - Make Table -
Categories].ID = [T - Make Table - North Field].ID) ON [T - Make Table -
Person].ID = [T - Make Table - Categories].ID) INNER JOIN [T - Make
Table -
Company] ON [T - Make Table - Person].ID = [T - Make Table - Company].ID)
INNER JOIN [T - Make Table - Dates] ON [T - Make Table - Person].ID = [T -
Make Table - Dates].ID) INNER JOIN [T - Make Table - Phone] ON [T - Make
Table - Person].ID = [T - Make Table - Phone].ID) INNER JOIN [T - Make
Table -
Returned Mail] ON [T - Make Table - Person].ID = [T - Make Table -
Returned
Mail].ID) INNER JOIN [T - Make Table - SRD RFP] ON [T - Make Table -
Person].
ID = [T - Make Table - SRD RFP].ID) INNER JOIN [T - Make Table - T2X Round
1]
ON [T - Make Table - Person].ID = [T - Make Table - T2X Round 1].ID) INNER
JOIN [T- Make Table - Duplicate Record] ON [T - Make Table - Person].ID =
[T-
Make Table - Duplicate Record].ID) INNER JOIN [T- Make Table - Source] ON
[T -
Make Table - Person].ID = [T- Make Table - Source].ID;
Click to expand...
 
J

John Vinson

Please help. I've created a query which is pulling data from 11 tables. The
query is not updatable.
Click to expand...

That's not a bit surprising.

Fortunately, it is neither common nor necessary to create One Great
Master Query that updates every table in your database.

Instead, design a logical layout of Forms (for the "one" side of
tables) and Subforms (for the "many" side tables); little maintenance
forms for the lookup tables; use Combo Boxes on the forms to store the
ID field from the lookup tables while displaying the looked-up text
value.


John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Question On Join only if Left table Items Is Not Null 4
SUM in a UNION query 2
query results in Access 3
A Team Required for an update query 1
How to prevent duplicates second go around 1
Need an Expert... Update with a DMAX 1
Making a query to get data from a parent-child table 1
Add a field in a query. Easy. . .so I though. 5

Top