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
Duane, Sorry for that i first did not opened the CrossTab application from Rogers Access Library. Now i did opened and viewed it.
How can i use it for my purpose? Could you please guide further.
Regards,
Khalid
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
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
Tidak ada komentar:
Posting Komentar