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

Duane,

Thanks once again for your Report. I have done some changes in the design as per my needs, like making Report Header, in Report Footer putting Totals of Cartons under each ConsignmentNo. Changing some Captions on the "frmRunCrosstab".

Although the report is perfect and i'm going to use it. You might have noticed in my data that in table "Consignment Number" for ConsignmentNo 2014-B-08 ExportDocs date is 15/01/15 that is why this ConsignmentNo is not displaying in Year 2014, but it appears in Year 2015. This situation might occur in future like ConsignmentNo is say 2015-A-10 and ExportDocs date is suppose 20/02/16.

For this i was thinking that should i add a new field like "ConsignmentYear" in table "Consignment Number" and while adding a new ConsignmentNo fill that field with Left 4 digits of ConsignmentNo, and then we just change in our all coding "ExportDocs" with "ConsignmentYear" so that we may get in our report all Consignments in their respective year.

Is my approach right? or i am on the wrong? Please guide.

In the report "ClientName" is displaying in Descending Order and in query "qryKhalid_DH" ClientCIN has Sort Order Ascending. I tried to add the Control "ClientCIN in report but it does not shows me ClientCIN in Ascending Order and ClientName in Descending Order. My requirement is to display ClientCIN in Ascending Order with their respective ClientName. I am unable to get it resolve.

Could you please be helpful again.

Thanks in advance.

Regards,

Khalid

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

Khalid and John,

I just uploaded a crosstab report demo using the same data. The report design allows for a predefined number of columns based only on the horizontal width of the page. If the actual crosstab would generate additional columns, they are automatically "wrapped" below the first set of columns. For instance if the columns were the month January - December and you only had width for 6 columns January - June would display first in a group and July - December would display later. There are realistically no limits to the number of column headings that could be generated with this solution.

There is much less code and probably more efficient with larger data sets.

It took me a while to review my demo and re-purpose it with Khalid's data. Let me know if you have any questions.

Duane Hookom, MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 18 Feb 2015 16:59:34 +0100
Subject: Re: [MS_AccessPros] Report based on Crosstab query

Khalid-

All you need are the rptKhalidMT report and the ztblKhalidReport table. You can rename the report and change the report header. If you want to rename the table, you will have to fix the code behind the report that references the table.

' Clear out the "working" table

db.Execute "DELETE * FROM ztblKhalidReport", dbFailOnError

and

' Open the output recordset

Set rstO = db.OpenRecordset("ztblKhalidReport", dbOpenDynaset, dbAppendOnly)

And you will have to change the Record Source of the report.

You should copy those two objects into your front end. Do not modify your existing backend.

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 18, 2015, at 4:26 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Marvelous!
I un-zipped the file you up-loaded PCTL_be. I did checked/run the report also viewed the query and table.

I'm sure my other tables would be as they were. I would also have to modify report header as per my requirement.

Can i re-name the table before linking & also re-name query and report. I will deeply observe the report's Open event.

Should i over write PCTL_be on my original file confidently?

Once again thank you very much John. You always have been great support and help to me.
Thanks to Duane also for being helpful and providing all assistance & help.

Regards,
Khalid

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

https://groups.yahoo.com/neo/groups/MS_Access_Professionals/files/2_AssistanceNeeded/PCTL_be.zip

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 18, 2015, at 1:39 PM, khalidtanweerburrah@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Congratulations! you got the solution.
But i cannot see any file, detail about table or some other thing. Are you still working on it?
Waiting...

Regards,
Khalid

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

Khalid-

I built a solution that uses a "working" table. The report prompts you for a year (you could also get the value from an open form), runs the Crosstab filtered for the year, then dumps the result into the working table. It modifies the captions on the labels to reflect the actual field names from the filtered data. Note that the sample report is set up to handle up to 10 Consignment Number values. If there are more than that in a year, it will warn you and display only the first 10.

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 18, 2015, at 12:41 PM, John Viescas JohnV@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Well, I tried to apply the KB article solution to Khalid's problem, but I ran into an ancient (since 2002) bug when the report tries to apply a sort to the modified Crosstab query. The error message is "Cannot use the crosstab of a non-fixed column as a subquery." Here's the old KB article on the error:

http://support.microsoft.com/KB/275070

It's probably going to require dumping the filtered result into a temp table, which I think is what Duane's solution does. I may play with it some more and will post again if I get a solution.

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

Duane,
I have uploaded the file PCTL_be in which you will find all the tables.

I hope this would suffice and you can do your working. Anxious for your achievement.

Regards,
Khalid

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

I fairly certain the solution would work. I would not use the whole unbound KB solution since it is bloated with code and less efficient. I would be will to try create a sample if I had the Clients and CollectionVoucher tables.

However, I wonder if a simple multi-column subreport would work. Does a ConsignmentNo link to more than one client? If not, base the main report on the unique ClientCIN only. Then display the ConsignmentNo and Count of CartonNo across then down. Allow the subreport grow.

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 17 Feb 2015 11:18:55 +0100
Subject: Re: [MS_AccessPros] Report based on Crosstab query

Duane-

I don't think your sample database is going to help Khalid.

Khalid-

You need to do something similar to the technique described in the KB article:

http://support.microsoft.com/kb/328320

Basically, you create unbound text boxes and companion labels in your report and give them names that can be easily indexed in code like txtBox01, txtBox02, etc. and lbl01, lbl02, etc. In the Open event of the Report, dynamically create the Record Source SQL adding the filter for the year you want. Then open the query as a Recordset and use the column names to set the label Caption properties. When you have more labels / text boxes defined that are returned by the query, hid the extra ones.

In the Format event of the Detail section, grab the fields from the current row and put them in the text boxes in a loop.

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

Duane,
Well thank you very much for describing the bases of your CrossTab Report.
This reminds me long ago when i do work in FoxPro DOS. I understand the concepts of DO WHILE and LOOPS in FoxPro. But i have up to now never tried this in Access.

At present i have a simple Report based on query "CollectionVoucher_Crosstab" with the 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;

As per current data this report shows 15 Columns for ConsignmentNo

Would these columns (ConsignmentNo) give us no problem as they will be increasing year by year. Approximately 8 - 10 or more new ConsignmentNo are allotted in a year. OR should we make our report for a specific Year of Consignment.

ConsignmentNo is Text field, Field Size 9, Input Mask 9999\-A\-99;;- where Left 4 digits denotes year of ConsignmentNo

Waiting for your further guidance and suggestions.
Regards,
Khalid

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

If your column names in your crosstab are going to vary over time and based on filters, the solution from the demo should be applicable. There is a table in the Access file that describes how the solution works.

This is the information from the table:
Explanation
Are you familiar with Crosstab Queries? Crosstab queries will dynamically create columns/fields based on each unique value of a particular field in your query. I my demo, I used customer names as columns.
The problem is:
1) the number of columns created by the crosstab will vary based on the number of customers during a particular period of time, the type of products included in your report, and the sales persons reported. There might be 2 or there might be 20. We don't know until the crosstab is run.

2) reports expect a certain number of fields in their record source. This generally isn't a problem since we base most reports on fields/columns from a query in which the fields don't change. The records will change but the same base columns will generally be available. Since Crosstab queries will vary in the number and names of fields, reporting them takes some work arounds.

My method starts with a desire to predefine the column/field names in the report. This could be numbers, colors, or whatever. I chose letters of the alphabet since they easily afford up to 26 columns, alphabetic order is something we all understand, they have an ASCII value that converts to numbers using the ASC() function, and "A" is easier to type then "Yellow".

I can now create an "alias" table of two fields, the customer name and the associated letter of the alphabet. If I include this table in my crosstab and link the customer names, I can use the associated letter as the column heading.

This works fine except that different sales people will sell to different customers. So we add a salesperson field to the "alias" table. We can create a totals query "qappEmpCust" that will append every unique combination of salesperson and customer to the Alias table. I then use code to loop through the table and assign the letters "A-F" or whatever.

This works fine until one salesperson sells to more than 6 customers. So, I add another field that describes the level (couldn't find a better term). The code loop then assigns level 0 letters A-F and then increments to level 1 and starts over at A etc.

The final piece was to provide column headings. This was done using a label type subreport where the link master child fields reference the Salesperson and Level displaying the customer names rather than A-F.

All of this together allows me to successfully report the result of a crosstab query regardless of the number of potential columns and their names. The report also runs much faster than a solution provided in the Microsoft sample Solutions.MDB.

Are you even more confused? To be honest with you, I have to re-think what I did each time a create a new report. One of my employees has used this method for about 6-8 reports. She figures this method has saved her a couple days of development time and kept a client much more satisfied.

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 16 Feb 2015 10:53:06 -0800
Subject: RE: [MS_AccessPros] Report based on Crosstab query