Jumat, 13 Februari 2015

Re: [MS_AccessPros] Report based on Crosstab query

 

John,

The sql is same as you given me.

PARAMETERS [Enter Export Year:] Long;
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;

Even then the same thing happening. I cannot modify the report in Design mode. Nor i can create a fresh report based on this query.

Regards,
Khalid



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

Khalid-

Because you are using a Crosstab query, you MUST explicitly declare the parameter.  Without it, you'll get that error message.  Either switch to SQL mode and add the PARAMETERS clause I gave you at the very beginning or choose Parameters from the Query menu.

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 11:19 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
The query works perfectly.

The first line of query:
PARAMETERS [Enter Export Year:] Long;
Should it be in the query or PARAMETERS have to been defined at some other place?

Because when i opened the report and entered Parameter Value on the previous Record Source and Sorting and Grouping which was on ClientCIN and it had all ConsignmentNo of CollectionVoucher. It gave me error message:
The Microsoft Jet Database engine does not recognize " as a valid field name or expression.

The i tried to open the report in Design view i was bound by the Enter Parameter value box. It could not close even on clicking Cancel button it was popping again and again. I have to stop it through Task Manager.

How to resolve this?

I am seeing query in Design mode as follows:
Field: ClientCIN     ConsignmentNo     CartonNo Total Of CartonNo: CartonNo  ConsignmentNo
Table: CollectionVoucher  CollectionVoucher  CollectionVoucher       CollectionVoucher CollectionVoucher
Total: Group By               Group By              Count          Count                                   Where
Crosstab:  Row Heading          Column Heading    Value          Row Heading        
Sort:         Ascending              Ascending 
Crieteria:                                                                                                  In (SELECT ConsignmentNo FROM [Consignment Number]


Regards,
Khalid


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

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@... [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:

ConsignmentNoExportDocs
2013-A-0904/11/2013
2013-A-1128/11/2013
2013-B-0805/10/2013
2013-B-1028/11/2013
2014-A-0108/02/2014
2014-A-0328/04/2014
2014-A-0430/05/2014
2014-A-0503/10/2014
2014-A-0730/11/2014
2014-A-0919/11/2014
2014-B-0215/05/2014
2014-B-0619/11/2014
2014-B-0815/01/2015
2015-A-0116/01/2015
2015-A-0206/02/2015
2015-A-0331/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: khalidtanweerburrah@yahoo.com
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (7)

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)