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
(Paris, France)
On Oct 23, 2014, at 8:41 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
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-
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.
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
(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 TextDeliveryVr is TextCartonSuffix is TextKhalid
__._,_.___
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) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar