 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2007Changing data from source tables using queries?
Hello - I have been having a frustrating time with what I think must have a simple solution: If I have an Access database with 2 tables, run a simple query and then output all the information from both tables why is it that sometimes I can manually type in new data whilst viewing the query output and other times Access just beeps at me and does not allow new data to be manually entered into the resulting query fields? Is this because primary keys are not defined or because relationships haven't been assigned? wdp >Hello - > [quoted text clipped - 10 lines] > >wdp Search the Help file for "Updateable". There are many reasons why queries may not allow updating. The absence of a primary key, as you correctly guess, is a common one. John W. Vinson [MVP] It is most likely because there is not a unique index on one side of the relationship. For more detail, see: Why is my query read-only? at: http://allenbrowne.com/ser-61.html  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Hello - > [quoted text clipped - 10 lines] > > wdp Thanks for the feedback! Typically none of my tables have primary keys defined because the data comes from multiple sources that I can't control. However, if I modify my tables and set a primary key I can link the two tables together in a query (the link MUST be to a primary key in one table) and can make the changes within the query output. This is helpful, however I am unable to create more than one link between the tables, only one - to a primary key - is allowed if I want to make changes to a table from within the query output. Does this make sense? I am surprised that I can't link more than one field between the tables? wdp You can join tables on mulitple fields if you wish. But the results will not be updatable unless you have a unique index on the combination of fields on the "one" side of the join. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Thanks for the feedback! > [quoted text clipped - 12 lines] > > wdp > But the results will not be updatable unless you have a unique index on the > combination of fields on the "one" side of the join. Allen- Can you clarify what you mean by this (I apologize): For example: Table 1: Customer (primary) Product Supplier Table 2 (no primary key): Customer Product Supplier If I run a query of these tables and link by Customer it is updateable. If I link by Customer and Product it is not (only tried when "link" is = in both tables, not all in one or other). Where does the "unique index on the combination of fields" come into play? wdp To create a unique index on the combination of your Customer + Product fields: 1. Open table 1 in design view. 2. Open the Indexes box (Tools menu.) 3. On a fresh line in the dialog, enter an index name and the first field, and set the Unique property (lower pane) to Yes. 4. On the next line of the dialog, leave the index name blank, and choose the 2nd field. SignatureAllen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. >> But the results will not be updatable unless you have a unique index on >> the [quoted text clipped - 19 lines] > when "link" is = in both tables, not all in one or other). Where does > the "unique index on the combination of fields" come into play? > I am surprised that I can't link more than one >field between the tables? I am too, since Access allows you to use up to ten fields to link tables. What is preventing you from doing so? John W. Vinson [MVP] On Jul 3, 11:26 am, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote: > > I am surprised that I can't link more than one > >field between the tables? [quoted text clipped - 4 lines] > > John W. Vinson [MVP] John- My apologies for the confusion. I can set up multiple links between two tables and run queries. The issue we're discussing arises if I want to update the data from within the results of a query. Using default table settings (which the indexing solution that Allen mentioned above is not) if I use multiple links, then it's not updateable. This is more than slightly inconvenient because while the indexing solution that Allen provided is helpful, it appears as though I'd have to set it for each table? wdp |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.