Senin, 23 Januari 2017

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

 

Khalid-


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

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

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



__._,_.___

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

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