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

Rabu, 22 Oktober 2014

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

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