?
Solved

Making the result set of a query involving more than one table updateable

Posted on 2013-01-29
7
Medium Priority
?
312 Views
Last Modified: 2013-01-29
I have joined two tables in a query on the first name and last name  (see attachment) . I plan to include this query in a form that will allow me to view the entries and make changes to them where applicable. The problem I am facing is that the result set from the query is not updateable. It only allows me to view the entries but not edit them. How can I remedy this situation?
Edit-Query-Results.jpg
0
Comment
Question by:geeta_m9
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38831630
To make that query updateable, you should be storing the Primary Key from the Applications table in the Statements table, and using that key field as the link between the two tables.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 1000 total points
ID: 38831636
Try this...

Add a primary key to the Statements table.  (ID)
Include both primary keys in the query output.

See if that gives you an updateable result.
0
 

Author Comment

by:geeta_m9
ID: 38831652
The primary key in both the tables is a combination of the first name and last name. You mean to say I have to have a single field as the primary key, and cannot use a combination of two fields?
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 

Author Comment

by:geeta_m9
ID: 38831662
I guess it is my fault...I did not specify it that way in the table design.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1000 total points
ID: 38831665
From your image, it looks like you have the ID field in the Applications table set as the primary key...  My suggestion was to add that as a foreign key to your other table and link on the ID instead of the two name fields (which you could then remove from the statements table).


Anyhow, try Pete's suggestion first.
0
 

Author Comment

by:geeta_m9
ID: 38831755
If I made the primary key a combination of the first name and last name like so (see attached), it seems to work. The ID was just an autonumber created by Access which I did not use. I already have hundreds of records in each of the tables and don't feel like going in and assigning the matching ID from the Applications table to each of the records in the other two tables. Next time, though I will use a valid ID field as it will save me a lot of trouble that way.
Combination-Primary-Key.jpg
0
 

Author Closing Comment

by:geeta_m9
ID: 38831775
Thanks!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question