Khalid-
That query should work as long as ProductID is now the Primary Key of the Products table. No number required in the UPDATE query. The subquery should return the ProductID of the row that matches for ProductNameEnglish.
ProductID in the GD-1 table should be a the same data type as the ProductID in Products, but should not be AutoNumber. Because it's a foreign key, it should be long integer if the ProductID in Products is AutoNumber.
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 Jan 20, 2017, at 9:28 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I have not yet done anything to my user's back end, nor yet i have given him .accde I am only working on my .accdb on my Laptop. Also i haven't made new .accde on my Laptop yet.
I am first trying on my .accdb whether the things are working like the one UPDATE query you are guiding me. I do not get my user's back end often, instead when he demands for minor changes i made it at my end. Then i get his back end and fix the tables and return him both back end and .accde.
I haven't replaced my Products table with a new table i have just added new field "ProductID" and field "ProductNameEnglish" was already there, before this field "ProductNameEnglish" was PK. Now "ProductID" is PK. And with discussion with my user through SMS i have entered Product ID's for each ProductNameEnglish. I again make it clear it is all done on my Laptop nothing has been done on my user's back end yet.
So trying and running this query on my Laptop:
UPDATE [GD-1]
SET ProductID = <-------- should there be any numeric ProductID OR this is the correct syntax. I am sorry for pointing it
(SELECT ProductID FROM Products WHERE ProductNameEnglish = [GD-1].ProductNameEnglish)
It is still giving error message:
Operation must use an updateable query
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
It's a shame you haven't kept your "back end" database in sync with you user's. You need to add ProductID to all his tables that have previously used ProductNameEnglish. Then update those tables by matching ProductNameEnglish with your new Products table.
You should be able to do the updates in one pass for each table after you add a ProductID field. Your SQL will never work. It should look like:
UPDATE [GD-1]
SET ProductID =
(SELECT ProductID FROM Products WHERE ProductNameEnglish = [GD-1].ProductNameEnglish)
This assumes you have replaced the Products table with your new table that has both ProductID and ProductNameEnglish.
After you run all the queries like above for all tables that use ProductNameEnglish as the foreign key, you should be able to delete the ProductNameEnglish field from those tables to use with your new front end.
You are probably working in a .accdb file to make your changes, but you give your user a .accde file to run so he can't make changes.
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 Jan 20, 2017, at 8:26 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Exactly i will do that what you suggested in the start. Please make my one concept clear when you write front end, does it means .accde ? i am always confused with this name front end i assume that front end is the file which i kept with me which is .accdb ? and if .accde is the front end what should i call .accdb ?
In my case, i will have to go his office and fill data in table "Products" he has added some more items in field "ProductNameEnglish" and also has added more than thousand records in table "CollectionVoucher" through data entry in form "New Cargo Collection Input".
Do you suggest that instead of going to his office i get access to his Laptop via TeamViewer OR get his back end via TeamViewer and then make necessary changes on my Laptop ?
You wrote: For each table in the back end that used to use ProductNameEnglish, add a field that matches the data type of ProductID.
I have used the same name "ProductID" for each table in my back end that used to use ProductNameEnglish, i think this won't make any blunder as i remembered once before i got confirmed it with you that i can use PK name of a table in another table.
Now i have tried for the UPDATE query which you gave. ProductNameEnglish is text type should ProductNameEnglish not be in quotes "" ?
UPDATE SomeTable
SET ProductID =
(SELECT ProductID from Products WHERE ProductNameEnglish = SomeTable.ProductNameEnglish)
I am getting syntax error while saving this SQL
UPDATE GD-1
SET ProductID = 6
(SELECT ProductID from Products WHERE ProductNameEnglish = GD-1."ARM PAD")
Please check and also answer my above questions.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
You should get a copy of his backend with all the data and fix it. Tell him to NOT make any more changes until you send him a new front end and back end. First thing, replace the table Products in his backend. For each table in the back end that used to use ProductNameEnglish, add a field that matches the data type of ProductID. Then run an UPDATE query for each table, something like:
UPDATE SomeTable
SET ProductID =
(SELECT ProductID from Products WHERE ProductNameEnglish = SomeTable.ProductNameEnglish)
If that all runs fine, remove ProductNameEnglish from all the old tables.
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 Jan 20, 2017, at 3:37 PM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi John,
As I have included a new field "ProductID" (PK) , Field Size Long Integer in table "Products", before field "ProductNameEnglish" was PK. I have changed relationships in all relevant tables for "ProductNameEnglish" to "ProductID".
Now I am modifying the forms having field "ProductNameEnglish" with field "ProductID", and in there querries I have added field "ProductID". For the forms having small data I will manually enter Product ID's for "ProductNameEnglish" and then remove field "ProductNameEnglish" from them.
A big fatigue is infront of me that when I'll make new .accde and put on my boss's Laptop I have given him two files:
PCTL_be.accdb
PCTL.accde
First I will enter "ProductID" in his table "Proucts" for each "ProductNameEnglish". Then how can I enter "ProductID" in his tables which have huge data for example table "CollectionVoucher" might be more than 15,000 records. Is there any way it could be done through some query or form which I run on his Laptop and things go OK, instead of I enter field "ProductID" manually on his file PCTL_be.accdb and then remove field "ProductNameEnglish".
Help required please.
Regards,
Khalid
__._,_.___
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 (6) |
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