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: khalidtanweerburrah@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
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