Rabu, 29 Oktober 2014

Re: [MS_AccessPros] Re: How to enter different product in the same carton

 

Khalid-


OK.  You said when you run this query:

SELECT DISTINCT CollectionVoucher.ClientCIN, Clients.ClientName
FROM Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN
ORDER BY CollectionVoucher.ClientCIN;

.. you get prompted for ClientName.

What happens if you run:

SELECT DISTINCT CollectionVoucher.ClientCIN
FROM CollectionVoucher;

??

If that gets you a prompt, you have a problem in your CollectionVoucher table.  If that does not fail, then it's looking like you might have some corruption in one of those two tables.  Have you tried a Compact and Repair?  If that doesn't help, then do this:

Create a new database and import just Clients and CollectionVoucher from your current database.  See if the SELECT DISTINCT .. INNER JOIN .. query runs in that.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Oct 29, 2014, at 5:19 AM, Khalid Tanweer khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

I simply ran this query and result is OK. Showing ClientCIN and its relative ClientName for 83 records.

Khalid


                     


On Tuesday, October 28, 2014 5:57 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:


 
Khalid-

The mystery continues.  Try this simple query:

SELECT ClientCIN, ClientName
FROM Clients


John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Oct 28, 2014, at 1:03 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Yes in CollectionVoucher table in ClientCIN field, on the Lookup tab only ClientCIN is defined.

SELECT Clients.ClientCIN
FROM Clients
ORDER BY Clients.ClientCIN;

Khalid




---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Very strange.  Take a look at the design for the CollectionVoucher table and the ClientCIN field.  Is there anything defined on the Lookup tab?  Perhaps you have an old combo box defined that is looking for ClientName in the CollectionVoucher table where it used to be.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Oct 28, 2014, at 12:36 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Yes! Before when i wrote Clients table has 83 records at that time i also did checked it in design view as well as in data sheet view.

Khalid


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Open the Clients table in Design view.  Do you see a ClientName field?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Oct 28, 2014, at 10:52 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Yes! i'm opening the Dialog from the report's Open event.

The query you gave:

SELECT DISTINCT CollectionVoucher.ClientCIN, Clients.ClientName
FROM Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN
ORDER BY CollectionVoucher.ClientCIN;

Did'nt worked, the same Parameter box is asking for ClientName. For Cartons Total i got it but my first preference is the above problem, which i am facing in other reports also.



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

Are you opening the dialog from the Report's Open event, or are you opening the dialog and then the report?

Does this query run OK?
SELECT DISTINCT CollectionVoucher.ClientCIN, Clients.ClientName
FROM Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN
ORDER BY CollectionVoucher.ClientCIN;

As for the totals, you need a query similar to what you had originally.

qryGroupCartons:
SELECT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, Sum(CollectionVoucher.WeightOfCarton) As CartonWeight, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1)) AS CartonSuffix
FROM CollectionVoucher
GROUP BY CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1));

Now in the footer, you can set controls to:

CartonCount:
=DCount("CartonNo", "qryGroupCartons", "ClientCIN = " & [ClientCIN] & " AND CartonNo = " & [CartonNo])

or

CartonWeight:
=DSum("CartonWeight", "qryGroupCartons", "ClientCIN = " & [ClientCIN] & " AND CartonNo = " & [CartonNo])




John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Oct 28, 2014, at 5:27 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

Clients table has field "ClientName" having 83 records the ClientCIN yet not allotted have been assigned the ClientName = (Blank).

So the questions arises why it cannot find the field ClientName in Clients table?

Khalid Tanweer
Manager Marketing & Exports
Pak Caspian Trade Links


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

I would guess it can't find a field called ClientName in the Clients table.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Oct 27, 2014, at 12:21 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
I tried this report with only this query "Freight Invoice Selective Consignment Client Query"

SELECT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, Clients.ClientName, Consignee.ConsigneeName, CollectionVoucher.Destination, CollectionVoucher.ExportDocs, CollectionVoucher.DeliveryVr, CollectionVoucher.DeliveryVrDate, CollectionVoucher.CartonNo, CollectionVoucher.CartonSuffix, CollectionVoucher.ProductNameEnglish, CollectionVoucher.ProductQty, CollectionVoucher.WeightOfCarton, CollectionVoucher.Rate, CollectionVoucher.Amount
FROM Consignee INNER JOIN (Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN) ON (Consignee.ConsigneeID = CollectionVoucher.ConsigneeID) AND (Consignee.ConsigneeID = Clients.ConsigneeID)
WHERE (((CollectionVoucher.ConsignmentNo)=[Forms]![Dialog Freight Invoice Selective Consignment-Client]![cmbConsignmentNo]) AND ((CollectionVoucher.ClientCIN)=[Forms]![Dialog Freight Invoice Selective Consignment-Client]![cmbClientCIN]));

The report is giving me the desired results in each row. Except what i asked before why "Enter parameter values..." message box opens and asking for "ClientName" on the Form "Dialog Freight Invoice Selective Consignment-Client" second combo box "cmbClientCIN" Its Control Source is unbound and Row Source is:

SELECT DISTINCT CollectionVoucher.ClientCIN, Clients.ClientName
FROM Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN
ORDER BY CollectionVoucher.ClientCIN;

Also only ClientCIN displays in drop down list, ClientName does'nt shows.

Secondly, on the reports "ConsignmentNo" footer i need the total of cartons grouped on text box "TotalCartons" presently its Control Source is: ="Total Cartons:" & Count(*)

Khalid
 


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Khalid-

First, you last query does not need to be a Totals query.  The total weight is already calculated in the qryGroupCartonsFreightInvoice query.

You cannot display the discrete carton number suffixes because of your requirement to total the weight for the multiple carton entries when more than one product is in a carton.  The rows showing up with a letter simply indicate duplicate CartonNo values that were moved from another assignment.  The total count of cartons should be correct because of the grouping.

I suppose you need to display the individual rows to display the different products.  To do that, you would have to use the raw CartonSuffix value and calculate your totals in a subtotal line on your report for each carton.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)




On Oct 26, 2014, at 8:53 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

After a short break i am back. In the meanwhile i did worked on a report "Collection Voucher" almost similar to the previous report we were working "Consignment Delivery Note". The same way i made a query to "Pre-Group" the Cartons, and all did worked correctly. (Thanks again to you).

Next report which i was working is "Freight Invoice Selective Consignment-Client". For this report i am getting two issues rest the report is working right.

While opening the report on the  Form "Dialog Freight Invoice Selective Consignment-Client" on clicking the first Unbound Combo box "cmbConsignmentNo" it works correctly having Row Source: 
SELECT DISTINCT CollectionVoucher.ConsignmentNo
FROM CollectionVoucher
ORDER BY CollectionVoucher.ConsignmentNo DESC;

On clicking second Unbound Combo box "cmbClientCIN" having Row Source:
SELECT DISTINCT CollectionVoucher.ClientCIN
FROM CollectionVoucher
ORDER BY CollectionVoucher.ClientCIN;

A Enter Parameter value box displays asking for "ClientName" on clicking OK button, drop down list displays ClientCIN and selecting any one report displays correctly even showing ClientName except that it shows alphabats in the column CartonSuffix but not Numerics.

To Pre-Group the Cartons i made the query:
qryGroupCartonsFrieghtInvoice:

SELECT CollectionVoucher.ConsigneeID, CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.Destination, CollectionVoucher.WeightOfCarton, CollectionVoucher.ExportDocs, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1)) AS CartonSuffix, CollectionVoucher.DeliveryVr, CollectionVoucher.DeliveryVrDate, CollectionVoucher.ProductNameEnglish, CollectionVoucher.ProductQty, CollectionVoucher.Rate, CollectionVoucher.Amount
FROM CollectionVoucher
WHERE (((CollectionVoucher.ClientCIN)=[Forms]![Dialog Freight Invoice Selective Consignment-Client]![cmbClientCIN]) AND ((CollectionVoucher.ConsignmentNo)=[Forms]![Dialog Freight Invoice Selective Consignment-Client]![cmbConsignmentNo]))
GROUP BY CollectionVoucher.ConsigneeID, CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.Destination, CollectionVoucher.WeightOfCarton, CollectionVoucher.ExportDocs, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1)), CollectionVoucher.DeliveryVr, CollectionVoucher.DeliveryVrDate, CollectionVoucher.ProductNameEnglish, CollectionVoucher.ProductQty, CollectionVoucher.Rate, CollectionVoucher.Amount;

And Reports Record Source is "Freight Invoice Selective Consignment Client Query":

SELECT qryGroupCartonsFrieghtInvoice.ConsignmentNo, qryGroupCartonsFrieghtInvoice.ClientCIN, Clients.ClientName, Consignee.ConsigneeName, qryGroupCartonsFrieghtInvoice.Destination, qryGroupCartonsFrieghtInvoice.ExportDocs, qryGroupCartonsFrieghtInvoice.DeliveryVr, qryGroupCartonsFrieghtInvoice.DeliveryVrDate, qryGroupCartonsFrieghtInvoice.CartonNo, qryGroupCartonsFrieghtInvoice.CartonSuffix, qryGroupCartonsFrieghtInvoice.ProductNameEnglish, qryGroupCartonsFrieghtInvoice.ProductQty, qryGroupCartonsFrieghtInvoice.WeightOfCarton, qryGroupCartonsFrieghtInvoice.Rate, qryGroupCartonsFrieghtInvoice.Amount
FROM Consignee INNER JOIN (qryGroupCartonsFrieghtInvoice INNER JOIN Clients ON qryGroupCartonsFrieghtInvoice.ClientCIN = Clients.ClientCIN) ON (Consignee.ConsigneeID = Clients.ConsigneeID) AND (Consignee.ConsigneeID = qryGroupCartonsFrieghtInvoice.ConsigneeID)
GROUP BY qryGroupCartonsFrieghtInvoice.ConsignmentNo, qryGroupCartonsFrieghtInvoice.ClientCIN, Clients.ClientName, Consignee.ConsigneeName, qryGroupCartonsFrieghtInvoice.Destination, qryGroupCartonsFrieghtInvoice.ExportDocs, qryGroupCartonsFrieghtInvoice.DeliveryVr, qryGroupCartonsFrieghtInvoice.DeliveryVrDate, qryGroupCartonsFrieghtInvoice.CartonNo, qryGroupCartonsFrieghtInvoice.CartonSuffix, qryGroupCartonsFrieghtInvoice.ProductNameEnglish, qryGroupCartonsFrieghtInvoice.ProductQty, qryGroupCartonsFrieghtInvoice.WeightOfCarton, qryGroupCartonsFrieghtInvoice.Rate, qryGroupCartonsFrieghtInvoice.Amount, qryGroupCartonsFrieghtInvoice.ConsigneeID;

Please guide where i am doing wrong.

Regards,
Khalid


 
 


---In MS_Access_Professionals@yahoogroups.com, <khalidtanweerburrah@...> wrote :

John,

OK! Thank you very much up to this m

(Message over 64 KB, truncated)




__._,_.___

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

.

__,_._,___

Tidak ada komentar:

Posting Komentar