Khalid-
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@yahoo.com [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.
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
What is the structure of GD-1? EVERY table should have a Primary Key.
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 11:04 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I don't have any idea which field to make Primary Key in this table. Can I have a try on another table "CollectionVoucher" having "ConsignmentNo" as PK ?
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
The table MUST have a Primary Key.
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 20, 2017, at 10:25 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Table GD-1 has no Primary key and field ProductID is empty in it, only "ProductNameEnglish" are filled.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Khalid-
John,
Does table GD-1 have a Primary Key? Before the update, the field ProductID in that table should be empty.
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 10:02 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
In table "Products" field "ProductID" is Primary Key, Long Integer, No Duplicates.
In table "GD-1" field "ProductID" has field size Long Integer, indexed: Yes (DuplicatesOK)
Its Lookup Row Source is:
SELECT Products.ProductID, Products.ProductNameEnglish
FROM Products
ORDER BY Products.ProductID;
As yet i have not removed field "ProductNameEnglish" from table "GD-1"
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 (18) |
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