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 |
#1
|
|||
|
|||
Query is not updatable -
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 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; -- Live Dangerously, but within the law... Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Query is not updatable -
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. |
#3
|
|||
|
|||
Query is not updatable -
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...DataQueryS.asp -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Doug Johnson via AccessMonster.com" u16546@uwe wrote in message news:5aa71f1166b2f@uwe... 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 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; -- Live Dangerously, but within the law... Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Query is not updatable -
On Fri, 20 Jan 2006 19:28:47 GMT, "Doug Johnson via AccessMonster.com"
u16546@uwe wrote: Please help. I've created a query which is pulling data from 11 tables. The query is not updatable. 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
How do I make this an Updatable Query? | cvegas | Running & Setting Up Queries | 1 | May 27th, 2005 07:24 PM |
Update Query - Operation Must Use An Updatable Query | Cheryl Lamonds | Running & Setting Up Queries | 1 | September 2nd, 2004 12:24 AM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM | |
Making a non updatable query into an updatable query | Daniel | Running & Setting Up Queries | 2 | June 16th, 2004 09:03 PM |