Repost:Crosstab 1

[finberg]

I am trying to make the crosstab query, which would be showing for each partner such table:
The list of books, the dates of orders and the numbers of the sertain book ordered.
When in constructor i ad the field Orderer and give the exact value- everething is going ok, but when in the field i enter [] For the request of the number of orderer, it gives me the error message "[] Couldn`t be recognized by the Mcirosoft Jet" What could it be?
And also I would like to use this query in the report.How can I make it (the dates of orders differs for each orderer.Thank you for your help



TRANSFORM Count([ShopsQ].Number) AS [Count-Number]
SELECT [ShopsQ].[Bookkode], [ShopsQ].Author, [ShopsQ].Name, Count([ShopsQ].Number) AS [Total Number]
FROM [ShopsQ]
GROUP BY [ShopsQ].[Bookkode], [ShopsQ].Author, [ShopsQ].Name
PIVOT Format([QDate],"Short Date"

Thank you
Arseniy

 

[danvlas]

If ShopsQ is a select query and you need to use a parameter to supply the cross-tab query with certain values, you have to define the parameter in ShopsQ.

Go to ShopsQ query design.
Query menu->Parameters
Enter the name of the parameter and its data type
The name of the parameter must coincide with whatever you type within [] (I truly hope it's something like
[Whatever]
and not plain [] which has no meaning).

HTH

Dan

 

[finberg]

Thank you very much for your help.It really the think i needed.May be you also would be so kind to advice something on the second question?

Thank you once more
Arseniy

 

[danvlas]

That involves some coding in almost all report event procedures, as you have to dynamically set the sources for the controls in your report, then loop through the recordset and populate it. That's because the number of columns may be different from orderer to orderer and the column names can also be different.

You have also to consider that you may run out of report space if there are too many columns. You have to decide which is the MAXIMUM number of columns your crosstab will ever return.

If you really want to do it, check the Solutions.mdb sample file: Create advanced reports-Create a crosstab report with dynamic column headings. You can even paste the code into your report module and change the names accordingly.

Good luck,

Dan