Re: [MS_AccessPros] Report based on Crosstab query

 

Khalid-

So, I could match on ConsignmentNo?  Maybe something like this:

PARAMETERS [Enter Export Year:] Integer; 

TRANSFORM Count(CollectionVoucher.CartonNo) AS CountOfCartonNo

SELECT CollectionVoucher.ClientCIN, Count(CollectionVoucher.CartonNo) AS [Total Of CartonNo]

FROM Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN

WHERE CollectionVoucher.ConsignmentNo IN 

  (SELECT ConsignmentNo FROM [Consignment Number] 

   WHERE Year([ExportDocs]) = [Enter Export Year:])

GROUP BY CollectionVoucher.ClientCIN

ORDER BY CollectionVoucher.ClientCIN, CollectionVoucher.ConsignmentNo

PIVOT CollectionVoucher.ConsignmentNo;

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 Feb 13, 2015, at 9:55 AM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi John,

CollectionVoucher table has a field DeliveryVrDate. But it is not in this query. Also this field would not give us proper results as Client gives his cargo on suppose in any date of December 2014 and we book it in ConsignmentNo for year 2015.

There is another table Consignment Number where i initialize ConsignmentNo this table has a Date field ExportDocs which have the date for the same year of ConsignmentNo Like:

ConsignmentNo	ExportDocs
2013-A-09	04/11/2013
2013-A-11	28/11/2013
2013-B-08	05/10/2013
2013-B-10	28/11/2013
2014-A-01	08/02/2014
2014-A-03	28/04/2014
2014-A-04	30/05/2014
2014-A-05	03/10/2014
2014-A-07	30/11/2014
2014-A-09	19/11/2014
2014-B-02	15/05/2014
2014-B-06	19/11/2014
2014-B-08	15/01/2015
2015-A-01	16/01/2015
2015-A-02	06/02/2015
2015-A-03	31/03/2015

see what you can get from here.

regards,

Khalid

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

Khalid-

Which table has a date in it?

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 Feb 13, 2015, at 6:29 AM, Khalid Tanweer khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi All,

        I have generated a report "Summary All Consignments" its Record Source is query "CollectionVoucher_Crosstab" with sql:

TRANSFORM Count(CollectionVoucher.CartonNo) AS CountOfCartonNo

SELECT CollectionVoucher.ClientCIN, Count(CollectionVoucher.CartonNo) AS [Total Of CartonNo]

FROM Clients INNER JOIN CollectionVoucher ON Clients.ClientCIN = CollectionVoucher.ClientCIN

GROUP BY CollectionVoucher.ClientCIN

ORDER BY CollectionVoucher.ClientCIN, CollectionVoucher.ConsignmentNo

PIVOT CollectionVoucher.ConsignmentNo;

Up to here things are fine. But i have a concern that when more/new ConsignmentNo are included in Collection Voucher how i will be able to manage them on one page (Landscape Left to Right). At present there are 15 ConsignmentNo in report in the Detail Section.

One option i was thinking about is that before opening the report year of Consignment be prompted and report should be based on the consignments for that particular year, in this way  report may adjust on a single page (Landscape left to right).

If we opt this option what would be the sql. Pattern of ConsignmentNo is 2015-A-03, Left 4 numbers denote year of Consignment.

Help needed please.

Regards

Khalid

 

__._,_.___

---

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

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___