OK, I got it to work. With no primary key in GD-1, it doesn't like the subquery, so I switched to DLookup.
Here's the sample data before the update:
| Consignment No. | ProductID | ProductNameEnglish |
| 2016-B-09 | | BOXING BANDAGE |
| 2015-A-06 | | SHIN PROTECTOR |
| 2015-A-06 | | SPORTS GLOVES |
| 2015-A-06 | | SOCK |
| 2015-A-06 | | BOXING GLOVES |
| 2015-A-06 | | ARM PAD |
| 2015-A-06 | | BALLS |
| 2015-A-03 | | ARM PAD |
| 2015-A-03 | | BALLS |
| 2015-B-07 | | ARM PAD |
| 2015-B-07 | | BALLS |
| 2015-A-01 | | ARM PAD |
| 2015-A-01 | | BALLS |
Then, I ran this query:
UPDATE [GD-1] SET [GD-1].ProductID = DLookup("ProductID", "Products", "ProductNameEnglish = " & "'" & [ProductNameEnglish] & "'")
And here's the result:
| Consignment No. | ProductID | ProductNameEnglish |
| 2016-B-09 | 9 | BOXING BANDAGE |
| 2015-A-06 | 5 | SHIN PROTECTOR |
| 2015-A-06 | 8 | SPORTS GLOVES |
| 2015-A-06 | 65 | SOCK |
| 2015-A-06 | 2 | BOXING GLOVES |
| 2015-A-06 | 6 | ARM PAD |
| 2015-A-06 | 1 | BALLS |
| 2015-A-03 | 6 | ARM PAD |
| 2015-A-03 | 1 | BALLS |
| 2015-B-07 | 6 | ARM PAD |
| 2015-B-07 | 1 | BALLS |
| 2015-A-01 | 6 | ARM PAD |
| 2015-A-01 | 1 | BALLS |
You should be able to do something similar with all your other tables where you need to replace ProductNameEnglish with the ProductID.
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 23, 2017, at 10:42 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Please check your email.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
I will be up for about another 90 minutes, so upload it, and I'll go get it. Or just send it privately to john at viescas dot com.
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 23, 2017, at 8:14 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Yes i deleted it last night. I waited for you late night to get your message you have copied it, now when you are there and stay for sometime, ask me i will upload it again, and after your confirmation i will delete it or if you can delete it yourself you should delete it from Assistance_Needed folder.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
I can't find the file. Did you delete it already?
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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 22, 2017, at 11:56 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I have uploaded file Database1 to folder Assistance Needed. I would request you to delete it after copying or tell me to delete it.
Thanks in advance,
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
That won't help. I need the two original tables to figure out why the query wouldn't work.
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 22, 2017, at 8:20 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I can copy the Products and GD-1 tables into a new database and then post that to Assistance Needed. But i don't want to show Products data.
If i delete the records in copied database and make it blank and you put your own data and work on it, would it be possible for you and acceptable ? Also note that in table GD-1 now i have removed field "ProductNameEnglish" you will have to add it to table GD-1 to check the query.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
I see no reason why the query refused to work on table GD-1. Perhaps you could copy the Products and GD-1 tables into a new database and then post that to Assistance Needed.
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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 22, 2017, at 5:52 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Yes, multiple rows per ConsignmentNo, each row for same ConsignmentNo and unique ProductID.
John, By the way i have put manually "ProductID" against "ProductNameEnglish" in table GD-1 and then removed field "ProductNameEnglish" and so on other tables where "ProductID" had to be put and removed "ProductNameEnglish". (Only on my machine and my data, assuming it to be test data).
However, i would appreciate if your Update query works, that will be helpful for me, when i get back end of my user and i may be able to apply this Update query on it.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
I don't remember ClientConsignments - that might have been a table that should have a compound Primary Key of ConsignmentNo and ClientID.
In table GD-1, are there multiple rows per ConsignmentNo? Perhaps the combination of ConsignmentNo and ProductID is unique?
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 22, 2017, at 4:27 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Structure of table GD-1:
ConsignmentNo-->Text 9
ExporterName-->Text 100-->Row Source-->SELECT ExporterGD1.ExporterName, ExporterGD1.ExporterTele FROM ExporterGD1 ORDER BY ExporterGD1.ExporterName;
PageOfPage-->Text 50
CustomOffice-->Text 50-->Row Source-->SELECT CustomOfficeGD1.CustomOffice FROM CustomOfficeGD1 ORDER BY CustomOfficeGD1.CustomOffice;
ImporterID-->Integer-->Row Source-->SELECT Importer.ImporterID, Importer.ImporterCountryEnglish, Importer.ImporterTelEnglish FROM Importer ORDER BY Importer.ImporterID;
Declarant-->Text 100-->Row Source-->SELECT DeclarantGD1.Declarant,
DeclarantGD1.DeclarantCityCountry, CountryOfDestination-->Text 5-->Row Source-->SELECT DestinationCountryGD1.CountryOfDestination FROM DestinationCountryGD1 ORDER BY DestinationCountryGD1.CountryOfDestination;
EFormNo-->Text 50
EFormDate-->Short Date
CurrencyName-->Text50
TotalValueDeclaredUnit-->Text 50
TotalInvoiceValue-->Long Integer
ExchangeRate-->Double,Fixed,4
PortOfShipment-->Text 50
PortOfDischarge-->Text 50
PlaceOfDeleivery-->Text 50
DeliveryTerms-->Text 50
NumberOfPackages-->Long Integer
GrossWeight-->Long Integer
ItemDescription-->Text 50
ItemNo-->Long Integer
NoOfUnits-->Long Integer
ProductID-->Long Integer-->Row Source-->SELECT Products.ProductID, Products.ProductNameEnglish FROM Products ORDER BY Products.ProductID;
UnitValueDeclared-->Number,Single
UnitValueName-->Text 50
TotalValueDeclared-->Long Integer
CustomValuePKR-->Long Integer
------------------------------------------------
Once you gave me structure of a table "ClientConsignments" and it did not have PK, Why ?
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Yes, you could try another table.
__._,_.___
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 (24) |
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