Sabtu, 14 Februari 2015

Re: [MS_AccessPros] Report based on Crosstab query

 

Khalid-


What are the column names of your query when you open it in Datasheet view for one of the years?

Are you not able to open the original report in Design View at all?

One of the problems with designing a report around a Crosstab query is the report designer engine tries to run the Record Source to find out the column names.  You might need to go with unbound text boxes and write some code to fill in the report.

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 Feb 14, 2015, at 1:24 PM, khalidtanweerburrah@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
I did everything as you said.
The query itself runs correct, starting with "Enter Parameter Value" prompt box and displays in datasheet view the year entered (currently i have data for 3 years, 2013, 2014 & 2015).

But i have still the problem with the report. I am yet unable to create a new/fresh report, nor i am able to modify the report in Design View as i had previously put the following controls on Detail Section
ClientCIN, Total Of CartonNo and all ConsignmentNo from year 2013 to 2015.

Now how could i resolve this issue.

Is there any way to create a new report? if i do try to create a new report through report wizard, when i select "CollectionVoucher_Crosstab" from available table/query there are no fields displayed, so report wizard could move to Next asking that at least 1 field must be selected.

Regards,
Khalid
 


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

Ah, I reread your post below, and now I see the problem.  It's going to prompt you at each row.  If you had entered the year over and over, it would have eventually displayed.

Here's what you need to do:

Create a blank form, put a text box on it.  Name the form frmYearPrompt.  Name the text box txtYear.  Put two command buttons on the form:  One with a caption of GO! (name it cmdGo), and the second with a caption of Cancel (name it cmdCancel).

In the click event of cmdGo, do this:

Private Sub cmdGo_Click()
    ' Hide me so the report can continue
    Me.Visible = False
End Sub

In the click event of cmdCancel, do this:

Private Sub cmdCancel_Click()
    ' Close me - the report will detect and cancel out
    DoCmd.Close acForm, Me.Name
End Sub

Change your query to read like this:

PARAMETERS [Forms]![frmYearPrompt]![txtYear] 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]) = [Forms]![frmYearPrompt]![txtYear])))
GROUP BY CollectionVoucher.ClientCIN
ORDER BY CollectionVoucher.ClientCIN, CollectionVoucher.ConsignmentNo
PIVOT CollectionVoucher.ConsignmentNo;

In the Open event of the Report, do this:

Private Sub Report_Open(Cancel As Integer)
    ' Open the parameter form
    ' Open as a dialog so this code will wait until
    '  the form either hides or closes
    DoCmd.OpenForm "frmYearPrompt", WindowMode:=acDialog
    ' Check to see if the parameter form is still open
    If Not CurrentProject.AllForms("frmYearPrompt").IsLoaded Then
        ' Not open anymore - cancel the report
        Cancel = True
    End If
    ' Finish and let it run
End Sub

Finally, in the Close event of the report, do this:

Private Sub Report_Close()
    ' If the parameter form is open,
    If CurrentProject.AllForms("frmYearPrompt").IsLoaded Then
        ' Close it to clean up
        DoCmd.Close acForm, "frmYearPrompt"
    End If
End Sub



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 14, 2015, at 10:54 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,
Yes this query does works and gives the ConsignmentNo of the year selected.

Khalid


On Feb 14, 2015, at 9:51 AM, John Viescas <johnv@...> wrote:

Khalid-

Does this query run?

SELECT ConsignmentNo FROM [Consignment Number]
WHERE Year([ExportDocs]) = [Enter Export Year:]

(Start a new query and paste that into the SQL Window.)

I just assumed that the name of the table you described below is Consignment 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 Feb 14, 2015, at 8:49 AM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

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: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (12)

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)