Thanks John, this is fantastic explanation.
Since this is a little bit harder to manage, is it really a good idea to keep the email, phone numbers, and addresses in different tables.
Thanks
On Monday, December 12, 2016 4:50 AM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jorge-
The problem is you have a query with related tables like this:
tblPersonsEmail <- persons -> tblPersonPhNumbers
That gets you the Cartesian Product between emails and phone numbers for each person - and that's not updatable. Let's say you have two emails for a person and three phone numbers. That should generate six (3 * 2) rows for that person.
To be able to edit this successfully in a form, you need to edit just the persons table in an outer form and then use two subforms - one each for emails and phone numbers, with both linked to person.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
http://www.viescas.com/
(Paris, France)
On Dec 11, 2016, at 11:46 PM, muralles_r12@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi John, thanks for getting back to me.
I did the following to trouble shoot the problem.
1. I design a query to get only the names (first and last name) these were in the tblPerson
2. I tried, I am able to edit the first and last name
3. I added a table, the tblPersonPhNumbers
4. I modified the query to show the phone number.
5. I can edit the data using the query still
6. I added the second table, tblPersonEmails
7. I modified the query to include the email field
8. I cannot longer update the data in data sheet
Here is the SQL
SELECT persons.personFirstName, persons.personLastName, tblPersonEmails.email
FROM (persons INNER JOIN tblPersonEmails ON persons.personID = tblPersonEmails.personID) INNER JOIN tblPersonPhNumbers ON persons.personID = tblPersonPhNumbers.personID;
Thanks for the help.
The problem happens when I add the second table.
I did it adding the email table first, the same problem.
Here is a copy of the data base if you like to take a look.
Jorge
On Sunday, December 11, 2016 3:55 PM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jorge,
The typical solution is to use subforms for the Phone, email and address records.
Regards,Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of muralles_r12@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Sunday, December 11, 2016 12:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] I cannot edit data using query or form
Good morning, here I am again, trying things out.
I am doing a tutorial and they suggested that I using a different table to keep phone numbers, email and address for a contact.
These is a fantastic idea, it allows me to enter multiple items for a customer: Phone, email and address.
I am using a query to pull out the customer name, last name, default email, phone and address.
It works well but I cannot edit the data in the table using the query.
I did a form base on the query, I cannot edit the fields there either.
What am I doing wrong?
Please help!!
The tables I am using are:
tblPersontblPersonEmailstblPersonPhNummerstblPersonAddresses Thanks
Jorge Muralles
#yiv3310690533 #yiv3310690533 -- #yiv3310690533ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3310690533 #yiv3310690533ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3310690533 #yiv3310690533ygrp-mkp #yiv3310690533hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3310690533 #yiv3310690533ygrp-mkp #yiv3310690533ads {margin-bottom:10px;}#yiv3310690533 #yiv3310690533ygrp-mkp .yiv3310690533ad {padding:0 0;}#yiv3310690533 #yiv3310690533ygrp-mkp .yiv3310690533ad p {margin:0;}#yiv3310690533 #yiv3310690533ygrp-mkp .yiv3310690533ad a {color:#0000ff;text-decoration:none;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ygrp-lc {font-family:Arial;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ygrp-lc #yiv3310690533hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ygrp-lc .yiv3310690533ad {margin-bottom:10px;padding:0 0;}#yiv3310690533 #yiv3310690533actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3310690533 #yiv3310690533activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3310690533 #yiv3310690533activity span {font-weight:700;}#yiv3310690533 #yiv3310690533activity span:first-child {text-transform:uppercase;}#yiv3310690533 #yiv3310690533activity span a {color:#5085b6;text-decoration:none;}#yiv3310690533 #yiv3310690533activity span span {color:#ff7900;}#yiv3310690533 #yiv3310690533activity span .yiv3310690533underline {text-decoration:underline;}#yiv3310690533 .yiv3310690533attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3310690533 .yiv3310690533attach div a {text-decoration:none;}#yiv3310690533 .yiv3310690533attach img {border:none;padding-right:5px;}#yiv3310690533 .yiv3310690533attach label {display:block;margin-bottom:5px;}#yiv3310690533 .yiv3310690533attach label a {text-decoration:none;}#yiv3310690533 blockquote {margin:0 0 0 4px;}#yiv3310690533 .yiv3310690533bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3310690533 .yiv3310690533bold a {text-decoration:none;}#yiv3310690533 dd.yiv3310690533last p a {font-family:Verdana;font-weight:700;}#yiv3310690533 dd.yiv3310690533last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3310690533 dd.yiv3310690533last p span.yiv3310690533yshortcuts {margin-right:0;}#yiv3310690533 div.yiv3310690533attach-table div div a {text-decoration:none;}#yiv3310690533 div.yiv3310690533attach-table {width:400px;}#yiv3310690533 div.yiv3310690533file-title a, #yiv3310690533 div.yiv3310690533file-title a:active, #yiv3310690533 div.yiv3310690533file-title a:hover, #yiv3310690533 div.yiv3310690533file-title a:visited {text-decoration:none;}#yiv3310690533 div.yiv3310690533photo-title a, #yiv3310690533 div.yiv3310690533photo-title a:active, #yiv3310690533 div.yiv3310690533photo-title a:hover, #yiv3310690533 div.yiv3310690533photo-title a:visited {text-decoration:none;}#yiv3310690533 div#yiv3310690533ygrp-mlmsg #yiv3310690533ygrp-msg p a span.yiv3310690533yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3310690533 .yiv3310690533green {color:#628c2a;}#yiv3310690533 .yiv3310690533MsoNormal {margin:0 0 0 0;}#yiv3310690533 o {font-size:0;}#yiv3310690533 #yiv3310690533photos div {float:left;width:72px;}#yiv3310690533 #yiv3310690533photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv3310690533 #yiv3310690533photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3310690533 #yiv3310690533reco-category {font-size:77%;}#yiv3310690533 #yiv3310690533reco-desc {font-size:77%;}#yiv3310690533 .yiv3310690533replbq {margin:4px;}#yiv3310690533 #yiv3310690533ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3310690533 #yiv3310690533ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3310690533 #yiv3310690533ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3310690533 #yiv3310690533ygrp-mlmsg select, #yiv3310690533 input, #yiv3310690533 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3310690533 #yiv3310690533ygrp-mlmsg pre, #yiv3310690533 code {font:115% monospace;}#yiv3310690533 #yiv3310690533ygrp-mlmsg * {line-height:1.22em;}#yiv3310690533 #yiv3310690533ygrp-mlmsg #yiv3310690533logo {padding-bottom:10px;}#yiv3310690533 #yiv3310690533ygrp-msg p a {font-family:Verdana;}#yiv3310690533 #yiv3310690533ygrp-msg p#yiv3310690533attach-count span {color:#1E66AE;font-weight:700;}#yiv3310690533 #yiv3310690533ygrp-reco #yiv3310690533reco-head {color:#ff7900;font-weight:700;}#yiv3310690533 #yiv3310690533ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ov li a {font-size:130%;text-decoration:none;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ov ul {margin:0;padding:0 0 0 8px;}#yiv3310690533 #yiv3310690533ygrp-text {font-family:Georgia;}#yiv3310690533 #yiv3310690533ygrp-text p {margin:0 0 1em 0;}#yiv3310690533 #yiv3310690533ygrp-text tt {font-size:120%;}#yiv3310690533 #yiv3310690533ygrp-vital ul li:last-child {border-right:none !important;}#yiv3310690533
[Non-text portions of this message have been removed]
------------------------------------
Posted by: <muralles_r12@yahoo.com>
------------------------------------
------------------------------------
Yahoo Groups Links
The problem is you have a query with related tables like this:
tblPersonsEmail <- persons -> tblPersonPhNumbers
That gets you the Cartesian Product between emails and phone numbers for each person - and that's not updatable. Let's say you have two emails for a person and three phone numbers. That should generate six (3 * 2) rows for that person.
To be able to edit this successfully in a form, you need to edit just the persons table in an outer form and then use two subforms - one each for emails and phone numbers, with both linked to person.
John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
http://www.viescas.com/
(Paris, France)
On Dec 11, 2016, at 11:46 PM, muralles_r12@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi John, thanks for getting back to me.
I did the following to trouble shoot the problem.
1. I design a query to get only the names (first and last name) these were in the tblPerson
2. I tried, I am able to edit the first and last name
3. I added a table, the tblPersonPhNumbers
4. I modified the query to show the phone number.
5. I can edit the data using the query still
6. I added the second table, tblPersonEmails
7. I modified the query to include the email field
8. I cannot longer update the data in data sheet
Here is the SQL
SELECT persons.personFirstName, persons.personLastName, tblPersonEmails.email
FROM (persons INNER JOIN tblPersonEmails ON persons.personID = tblPersonEmails.personID) INNER JOIN tblPersonPhNumbers ON persons.personID = tblPersonPhNumbers.personID;
Thanks for the help.
The problem happens when I add the second table.
I did it adding the email table first, the same problem.
Here is a copy of the data base if you like to take a look.
Jorge
On Sunday, December 11, 2016 3:55 PM, "Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:
Jorge,
The typical solution is to use subforms for the Phone, email and address records.
Regards,Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of muralles_r12@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Sunday, December 11, 2016 12:27 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] I cannot edit data using query or form
Good morning, here I am again, trying things out.
I am doing a tutorial and they suggested that I using a different table to keep phone numbers, email and address for a contact.
These is a fantastic idea, it allows me to enter multiple items for a customer: Phone, email and address.
I am using a query to pull out the customer name, last name, default email, phone and address.
It works well but I cannot edit the data in the table using the query.
I did a form base on the query, I cannot edit the fields there either.
What am I doing wrong?
Please help!!
The tables I am using are:
tblPersontblPersonEmailstblPersonPhNummerstblPersonAddresses Thanks
Jorge Muralles
#yiv3310690533 #yiv3310690533 -- #yiv3310690533ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3310690533 #yiv3310690533ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3310690533 #yiv3310690533ygrp-mkp #yiv3310690533hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3310690533 #yiv3310690533ygrp-mkp #yiv3310690533ads {margin-bottom:10px;}#yiv3310690533 #yiv3310690533ygrp-mkp .yiv3310690533ad {padding:0 0;}#yiv3310690533 #yiv3310690533ygrp-mkp .yiv3310690533ad p {margin:0;}#yiv3310690533 #yiv3310690533ygrp-mkp .yiv3310690533ad a {color:#0000ff;text-decoration:none;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ygrp-lc {font-family:Arial;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ygrp-lc #yiv3310690533hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ygrp-lc .yiv3310690533ad {margin-bottom:10px;padding:0 0;}#yiv3310690533 #yiv3310690533actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3310690533 #yiv3310690533activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3310690533 #yiv3310690533activity span {font-weight:700;}#yiv3310690533 #yiv3310690533activity span:first-child {text-transform:uppercase;}#yiv3310690533 #yiv3310690533activity span a {color:#5085b6;text-decoration:none;}#yiv3310690533 #yiv3310690533activity span span {color:#ff7900;}#yiv3310690533 #yiv3310690533activity span .yiv3310690533underline {text-decoration:underline;}#yiv3310690533 .yiv3310690533attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3310690533 .yiv3310690533attach div a {text-decoration:none;}#yiv3310690533 .yiv3310690533attach img {border:none;padding-right:5px;}#yiv3310690533 .yiv3310690533attach label {display:block;margin-bottom:5px;}#yiv3310690533 .yiv3310690533attach label a {text-decoration:none;}#yiv3310690533 blockquote {margin:0 0 0 4px;}#yiv3310690533 .yiv3310690533bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3310690533 .yiv3310690533bold a {text-decoration:none;}#yiv3310690533 dd.yiv3310690533last p a {font-family:Verdana;font-weight:700;}#yiv3310690533 dd.yiv3310690533last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3310690533 dd.yiv3310690533last p span.yiv3310690533yshortcuts {margin-right:0;}#yiv3310690533 div.yiv3310690533attach-table div div a {text-decoration:none;}#yiv3310690533 div.yiv3310690533attach-table {width:400px;}#yiv3310690533 div.yiv3310690533file-title a, #yiv3310690533 div.yiv3310690533file-title a:active, #yiv3310690533 div.yiv3310690533file-title a:hover, #yiv3310690533 div.yiv3310690533file-title a:visited {text-decoration:none;}#yiv3310690533 div.yiv3310690533photo-title a, #yiv3310690533 div.yiv3310690533photo-title a:active, #yiv3310690533 div.yiv3310690533photo-title a:hover, #yiv3310690533 div.yiv3310690533photo-title a:visited {text-decoration:none;}#yiv3310690533 div#yiv3310690533ygrp-mlmsg #yiv3310690533ygrp-msg p a span.yiv3310690533yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3310690533 .yiv3310690533green {color:#628c2a;}#yiv3310690533 .yiv3310690533MsoNormal {margin:0 0 0 0;}#yiv3310690533 o {font-size:0;}#yiv3310690533 #yiv3310690533photos div {float:left;width:72px;}#yiv3310690533 #yiv3310690533photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv3310690533 #yiv3310690533photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3310690533 #yiv3310690533reco-category {font-size:77%;}#yiv3310690533 #yiv3310690533reco-desc {font-size:77%;}#yiv3310690533 .yiv3310690533replbq {margin:4px;}#yiv3310690533 #yiv3310690533ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3310690533 #yiv3310690533ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3310690533 #yiv3310690533ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3310690533 #yiv3310690533ygrp-mlmsg select, #yiv3310690533 input, #yiv3310690533 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3310690533 #yiv3310690533ygrp-mlmsg pre, #yiv3310690533 code {font:115% monospace;}#yiv3310690533 #yiv3310690533ygrp-mlmsg * {line-height:1.22em;}#yiv3310690533 #yiv3310690533ygrp-mlmsg #yiv3310690533logo {padding-bottom:10px;}#yiv3310690533 #yiv3310690533ygrp-msg p a {font-family:Verdana;}#yiv3310690533 #yiv3310690533ygrp-msg p#yiv3310690533attach-count span {color:#1E66AE;font-weight:700;}#yiv3310690533 #yiv3310690533ygrp-reco #yiv3310690533reco-head {color:#ff7900;font-weight:700;}#yiv3310690533 #yiv3310690533ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ov li a {font-size:130%;text-decoration:none;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3310690533 #yiv3310690533ygrp-sponsor #yiv3310690533ov ul {margin:0;padding:0 0 0 8px;}#yiv3310690533 #yiv3310690533ygrp-text {font-family:Georgia;}#yiv3310690533 #yiv3310690533ygrp-text p {margin:0 0 1em 0;}#yiv3310690533 #yiv3310690533ygrp-text tt {font-size:120%;}#yiv3310690533 #yiv3310690533ygrp-vital ul li:last-child {border-right:none !important;}#yiv3310690533
[Non-text portions of this message have been removed]
------------------------------------
Posted by: <muralles_r12@yahoo.com>
------------------------------------
------------------------------------
Yahoo Groups Links
__._,_.___
Posted by: <muralles_r12@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
.
__,_._,___
Tidak ada komentar:
Posting Komentar