Ooops. Try this:
UPDATE Members SET [Passport IssueDate] = DLookUp("[Passport IssueDate]","tblPassport","MemberID = " & [MemberID]);
UPDATE Members SET Members.PassportExpiryDate = DLookUp("PassportExpiryDate","tblPassport","MemberID = " & [MemberID]);
UPDATE Members SET Members.PassportIssuePlace = DLookUp("PassportIssuePlace","tblPassport","MemberID = " & [MemberID]);
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
(Paris, France)
On May 12, 2017, at 6:16 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Yes there is space in "Passport IssuePlace"
While putting in sql:
UPDATE Members SET [Passport IssueDate] = DLookUp("[Passport IssueDate]","tblPassport","MemberID = " [MemberID]);
I get error message:
Syntax error (missing operator) in query expression 'DLookup("[Passport IssueDate]","tblPassport,"MemberID="[MemberID]);
I am running these queries one by one separately.
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :
Did you run these three queries?
UPDATE Members SET [Passport IssueDate] = DLookUp("[Passport IssueDate]","tblPassport","MemberID = " [MemberID]);
UPDATE Members SET Members.PassportExpiryDate = DLookUp("PassportExpiryDate","tblPassport","MemberID = " [MemberID]);
UPDATE Members SET Members.PassportIssuePlace = DLookUp("PassportIssuePlace","tblPassport","MemberID = " [MemberID]);
It looks like you have a space in the name of Passport IssueDate, so I added that.
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
(Paris, France)
On May 12, 2017, at 5:15 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I still cannot fill blanks in Members table for the following:
Passport IssueDate
PassportExpiryDate
above are Date type
PassportIssuePlace --> Text
---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Yes i replied you after checking that it updated field PassportNo in Members table.
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Yes, after you get rid of the quotes. Did you check the Members table to see if it updated correctly?
John Viescas, author
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
On May 11, 2017, at 22:13, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Yes, now it did worked for PassportNo
Should i run the other three Sql's one by one ?
Passport IssueDate
PassportExpiryDate
above are Date type
PassportIssuePlace --> Text
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
If MemberID is a number, get rid of the quotes:
UPDATE Members SET Members.PassportNo = DLookUp("PassportNo","tblPassport","MemberID = " & [MemberID]);
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
(Paris, France)
On May 11, 2017, at 9:31 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Sorry for replying late,, i was looking on the tab of my yahoo mail for a new email and it didn't showed up, i manually refreshed the group and saw your reply.
Should i run these update codes one by one separately ? I tried it with the first one.
Sql:
UPDATE Members SET Members.PassportNo = DLookUp("PassportNo","tblPassport","MemberID = " & "'" & [MemberID] & "'");
query grid at bottom
Field: PassportNo
Table: Members
Update to: DLookUp("PassportNo","tblPassport","MemberID = " & "'" & [MemberID] & "'")
Got no result. Error message was:
....didn't update 609 field(s) due to a type conversion failure, .....
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Nope. Remember, you're trying to copy the "blank" fields.
John,
---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :
UPDATE Members SET Members.PassportNo = DLookUp("PassportNo","tblPassport","MemberID = " & "'" & [MemberID] & "'");
UPDATE Members SET PassportIssueDate = DLookUp("PassportIssueDate","tblPassport","MemberID = " & "'" & [MemberID] & "'");
UPDATE Members SET Members.PassportExpiryDate = DLookUp("PassportExpiryDate","tblPassport","MemberID = " & "'" & [MemberID] & "'");
UPDATE Members SET Members.PassportIssuePlace = DLookUp("PassportIssuePlace","tblPassport","MemberID = " & "'" & [MemberID] & "'");
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
(Paris, France)
On May 11, 2017, at 7:40 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I have now added following fields in Members table:
PassportNo
Passport IssueDate
PassportExpiryDate
PassportIssuePlace
Obviously these all fields are blank now. I now want them to copy them from tblPassport to Members table.
I tried it with a update query you gave me for my other database (PCTL) where it did worked and solved the issue. But i made mistake by modifying its SQL and it deleted MemberID from tblPassport having 96 records.
I retrieved tblPassport from my backup. Now could you please correct the SQL of update query.
tblPassport has following fields:
MemberID
PassportNo
Passport IssueDate
PassportExpiryDate
PassportIssuePlace
My wrong SQL is as follows in which i did not mentioned:
Passport IssueDate
PassportExpiryDate
PassportIssuePlace
These are also to be included:
UPDATE tblPassport SET tblPassport.MemberID = DLookUp("MemberID","Members","MemberID = " & "'" & [MemberID] & "'");
I think it should be:
UPDATE Members SET Members.MemberID = DLookUp("MemberID","tblPassport","MemberID = " & "'" & [MemberID] & "'");
Khalid
---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
I have a Form "Add New Member" (Default View--> Single). On this Form I have put "TabCtlMemberInfo"
having some pages of members information, one of them is "PagePassport" and it has Form "frmPassportSubform" -->Record Source---> tblPassport with following fields:
PassportNo
Passport IssueDate
PassportExpiryDate
cboPassportIssuePlace
Now if i put these back to Members table, should i keep "frmPassportSubform" changing its Record source to Members table ?
Also putting PassportNo and other information in Members table against each member would be a fatigue for me.
Any way i will follow your suggestion.
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
If you're saving only one passport, then put it in the Members table.
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
(Paris, France)
On May 10, 2017, at 9:55 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
There will be single entry for each member who is participating in CST (Cultural Study Tour) and we saving only one Passport details of each member:
So please guide further.
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
Your query is not updatable because of the relationships.
CST Transaction M <- 1 Members 1 -> M tblPassport
That creates a Cartesian product between transactions an passports for each member. For example, if there are 9 transactions for a member who has two passports, you get 18 rows (9 * 2). A multiple table join works only if the relationships keep going "down" to lower levels: tblA -> tblB -> tblC.
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
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (16) |
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