ACCESS FEED: RE: [MS_AccessPros] Report based on Crosstab query

Senin, 16 Februari 2015

RE: [MS_AccessPros] Report based on Crosstab query

Khalid,

Did you try the version available from RogersAccessLibrary.com? It should have opened fine in Access 2003. When you asked for an updated version, I assumed you had Access 2010 or later.

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Sun, 15 Feb 2015 23:12:09 -0800
Subject: RE: [MS_AccessPros] Report based on Crosstab query

Duane,

i have downloaded the file and un-zipped it, it has the extension .accdb. I think this is Access 2007 file and i am using Microsoft Office 2003 with Access 2003, when i try to open it from Access 2003 i cannot see it in the list.

What should i do. I also need to know if i install Office 2007 and run Access 2007, will i face problems in running my database in Access 2003 into Access 2007 and in future will i face problems in doing coding and would i be able to do it up to my present knowledge of Access 2003?

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

In case you missed the announcement email:
https://groups.yahoo.com/neo/groups/MS_Access_Professionals/files/1_Samples/CrossTabReport.zip

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Sun, 15 Feb 2015 06:52:14 -0800
Subject: RE: [MS_AccessPros] Report based on Crosstab query

Duane,

I would really appreciate if you give the updated version of the link.

Regards,

Khalid

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

There is a solution for creating crosstab reports at http://www.rogersaccesslibrary.com/forum/cross-tab_topic11.html. This solution allows the creation of any number of columns (you decide). If you want 10 columns but 13 columns are generated, the 3 extra are rendered under the first 10.

It looks like the demo file is quite old. If you need an updated version, let me know.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Sat, 14 Feb 2015 06:36:17 -0800
Subject: Re: [MS_AccessPros] Report based on Crosstab query

John,

When i open the query in Datasheet view, Column names for year 2014 are :

2014-A-01, 2014-A-03, 2014-A-04, 2014-A-05, 2014-A-09, 2014-B-02, 2014-B-06

I am able to open the report in Design View. First of all "Enter Parameter Value" message box displays and except of this box i could not click anywhere else. Entering year there, it popups again, and then again entering year it goes/hide. After that if i do cut or edit any control that message box appears again and then it does'nt closes, even as many times i enter year. The only option which i am left is to open the Task Manager and close the database.

Regards,

Khalid

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

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

http://www.viescas.com/

(Paris, France)

On Feb 14, 2015, at 1:24 PM, khalidtanweerburrah@... [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: