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

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

http://www.viescas.com/

(Paris, France)

On Oct 26, 2014, at 8:53 AM, khalidtanweerburrah@yahoo.com [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 milestone, let me check other forms and reports. If there is some error or confusion i will most probably try to figure it out myself and try to resolve the logic you gave me. Else i'll knock your door and disturb you again.

Thanks again,

Khalid

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

Khalid-

When you have duplicate cartons, one can use the number suffix, but the others should use letters. It's also OK to use letters for all the duplicates. When you assign numbers only, that's the same as multiple products in ONE carton, so the query is eliminating them.

Yes, I understand that CartonSuffix is text, but if the first digit is a number, then the query effectively groups all in the same CartonNo as one to get the correct count.

So, the rule is:

Use numbers for multiple products in a carton.

Use letters for duplicate cartons moved from another consignment. If duplicate cartons with letters have multiple products, use a letter and a number.

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

http://www.viescas.com/

(Paris, France)

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

John,

First of all thanks indeed for being so supportive and generous for this help and guidance.

As you pointed out why i am getting ClientName from tbl CollectionVoucher i removed it from it, as it was my mistake while you told me before no table should have duplicate fields RULE NO. 1, only Pkey's can be part of other tables.

Then i modified your both queries slightly after removing ClientName from tbl CollectionVoucher.

qryGroupCartons:
SELECT CollectionVoucher.ConsigneeID, CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.Destination, Sum(CollectionVoucher.WeightOfCarton) AS Weight, CollectionVoucher.ExportDocs, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1)) AS CartonSuffix
FROM CollectionVoucher
WHERE (((CollectionVoucher.ConsignmentNo)=[Forms]![Dialog Consignment Delivery Note]![cmbConsignmentNo]))
GROUP BY CollectionVoucher.ConsigneeID, CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.Destination, CollectionVoucher.ExportDocs, CollectionVoucher.CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1));

Consignment Delivery Note Query:
SELECT qryGroupCartons.ConsignmentNo, qryGroupCartons.ClientCIN, Clients.ClientName, Consignee.ConsigneeName, qryGroupCartons.Destination, Count(qryGroupCartons.CartonNo) AS TotalCartons, Sum(qryGroupCartons.Weight) AS TotalWeight, qryGroupCartons.ExportDocs
FROM (Consignee INNER JOIN qryGroupCartons ON Consignee.ConsigneeID = qryGroupCartons.ConsigneeID) INNER JOIN Clients ON (Consignee.ConsigneeID = Clients.ConsigneeID) AND (qryGroupCartons.ClientCIN = Clients.ClientCIN)
GROUP BY qryGroupCartons.ConsignmentNo, qryGroupCartons.ClientCIN, Clients.ClientName, Consignee.ConsigneeName, qryGroupCartons.Destination, qryGroupCartons.ExportDocs;

-----------------------------------------
Now one thing i'm not getting that in qryGroupCartons last line of sql
IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix,1)),"",Left(CollectionVoucher.CartonSuffix,1));

Why you used IsNumeric ?
Field CartonSuffix is Text, and its Default Value is set to 0 (Should it be ZERO ?)

Secondly, now the report is OK, BUT i'm confused in CartonSuffix. On my previously entered data for ConsignmentNo 2014-A-05, ClientCIN 15 has delivered 81 cartons.

CartonNo 1 - 36 have been received against DeliveryVr 075
CartonNo 1 - 45 have been received against DeliveryVr 084

Duplicate CartonNo are from 1 - 36
If i assign CartonSuffix for these 1 - 36 duplicate cartons as below the report is OK.

CartonNo CartonSuffix
1 A
1 B
2 A
2 B
up to CartonNo 36
36 A
36 B
Remaining
37 0
38 0
39 0
40 0
41 0
42 0
43 0
44 0
45 0
But if i assign CartonSuffix as 0,1 OR 1,2 for CartonNo 1 -36, remaining 37 - 45 CartonSuffix as 0, then report shows Total Cartons for this Client = 45.

In this scenario what way i should adopt to assign CartonSuffix?

The delay in replying you was this issue, which i was checking.

Once lot of thanks up to here.

Khalid

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

Khalid-

Sorry I missed the bracket!

I assume you're now also using CartonSuffix with letters to indicate cartons moved from another assignment with duplicates. For example, if a carton 1 was moved, its CartonSuffix would be A1. If a moved Carton has multiple products, then you would use A2, A3, etc.

First, build a query on CollectionVoucher that "pre-groups" the cartons containing multiple products:

qryGroupCartons:
SELECT ConsigneeID, ConsignmentNo, ClientCIN, ClientName, Destination, Sum(WeightOfCarton) As Weight, ExportDocs, CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix, 1)), "", Left(CollectionVoucher.CartonSuffix, 1)) As CartonSuffix
FROM CollectionVoucher
WHERE (((CollectionVoucher.ConsignmentNo)=[Forms]![Dialog Consignment Delivery Note]![cmbConsignmentNo]))
GROUP BY ConsigneeID, ConsignmentNo, ClientCIN, ClientName, Destination, ExportDocs, CartonNo, IIf(IsNumeric(Left(CollectionVoucher.CartonSuffix, 1)), "", Left(CollectionVoucher.CartonSuffix, 1));

Now substitute this query in your final query:

SELECT qryGroupCartons.ConsignmentNo, qryGroupCartons.ClientCIN, qryGroupCartons.ClientName, Consignee.ConsigneeName, qryGroupCartons.Destination, Count(qryGroupCartons.CartonNo) AS TotalCartons, Sum(qryGroupCartons.Weight) AS TotalWeight, qryGroupCartons.ExportDocs
FROM Consignee INNER JOIN qryGroupCartons ON Consignee.ConsigneeID = qryGroupCartons.ConsigneeID
GROUP BY qryGroupCartons.ConsignmentNo, qryGroupCartons.ClientCIN, qryGroupCartons.ClientName, Consignee.ConsigneeName, qryGroupCartons.Destination, qryGroupCartons.ExportDocs;

The first query groups the rows in CollectionVoucher by CartonNo and "" when the CartonSuffix is just a number and by CartonNo and the letter when the CartonNo contains a letter indicating a duplicate CartonNo. Your final query now counts the rows grouped by the first query.

By the way, why are you getting ClientName from CollectionVoucher? I would think ClientName would appear in the Clients table linked by ClientCIN. When you introduce a duplicate field like this, you make your data subject to errors if the name gets misspelled when entered in the second table. This would mean an additional join to the Clients table to get the name, but you avoid any data entry errors.

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
http://www.viescas.com/
(Paris, France)

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

John,

Sorry that i was missing left bracket [ before CartonSuffix. Its OK now.

Now coming to one of a report "Consignment Delivery Note" its Record Source is a query with sql:

SELECT CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.ClientName, Consignee.ConsigneeName, CollectionVoucher.Destination, Count(CollectionVoucher.CartonNo) AS TotalCartons, Sum(CollectionVoucher.WeightOfCarton) AS TotalWeight, CollectionVoucher.ExportDocs
FROM Consignee INNER JOIN CollectionVoucher ON Consignee.ConsigneeID = CollectionVoucher.ConsigneeID
GROUP BY CollectionVoucher.ConsignmentNo, CollectionVoucher.ClientCIN, CollectionVoucher.ClientName, Consignee.ConsigneeName, CollectionVoucher.Destination, CollectionVoucher.ExportDocs
HAVING (((CollectionVoucher.ConsignmentNo)=[Forms]![Dialog Consignment Delivery Note]![cmbConsignmentNo]));

I have entered some test data for ClientCIN 2

CartonNo CartonSuffix

1 1
2 1
3 1
3 2
3 3
CartonNo 3 containing three different Products

Now on the Report i need that it should show me Total Cartons for this Client THREE i.e CartonNo 1,2 & 3
but i get Total Cartons = 5 which is count of CartonNo

Sorting and Grouping is of Report is:
Destination --- Group Header -- Yes
ClientCIN --- Group Header -- No
TotalCartons ---- Group Header -- No

Where i'm wrong? and what should i do to get Report as i need?
table CollectionVoucher has Pkey ConsignmentNo, ClientCIN, CartonNo and CartonSuffix

Khalid

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

Khalid-

OK, my mistake. Something is missing in your criteria. Try adding a Debug.Print stLinkCriteria after you assign the predicate. You can see the result in the Immediate Window. (CTRL+G)

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
http://www.viescas.com/
(Paris, France)

P.S. When replying on the web, click Show Message History at the bottom of the message area so I can see previous replies in email. I have to keep going back to the group website to look at previous answers.

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

John,

No! ClientCIN is number and CartonNo is number.

ConsignmentNo is Text
DeliveryVr is Text
CartonSuffix is Text

Khalid