Minggu, 22 Januari 2017

Re: [MS_AccessPros] Update a new field having huge data (Access 2007)

 

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 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@yahoo.com [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-

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.

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 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-

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 (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