Cross-Tab Report?

[WCStarks]

I would like to produce a report, which I believe would be a cross-tab report. See the sample Access Report and Excel Report.
I would like to produce a report similar to the Excel report, from a report similar to the Access report.
The cross-tab report would include the name and phone number (text deleted) from the member Table, and the Activity and Beg_Date, from the associated Assignments Table.



Am I correct is calling this a cross-tab report? Can this be done? I have never produced a cross-tab report before, and am not sure how to do it. I didn't find the MS instruction very meaningful for me. See inline attachments.

[Bulzie]

Not sure what you are asking. You want those fields you mentioned to be used in a Crosstab query/report which would look something like the excel report? If you want to try doing a crosstab query/report, create a query that contains your data fields(unless they are in same table), then use the Query Wizard(then select crosstab) on that query to see if you can get the output to be as you want.

[WCStarks]

OK. I have a query called, Trips by YearQ, which produces the data needed for the indicated Access report. It contains many more fields than needed for the cross-tab query/report, but it includes all those needed for the cross-tab. If I understand you correctly, I now create a cross-tab query that gets its data from "Trips by YearQ", selecting only the elements needed for the cross-tab query. Then I will use the cross-tab query as the data source for the cross-tab report?

[WCStarks]

I have the following cross-tab SQL

TRANSFORM First([Trips by YearQ].[Activity]) AS FirstOfActivity
SELECT [Trips by YearQ].[KeyName]
FROM [Trips by YearQ]
GROUP BY [Trips by YearQ].[KeyName]
PIVOT Format([Beg_Date],"Short Date");

[Trips by YearQ] query requests you to enter a year to produce the query for, and stores that into a variable called [This_Year]. The cross-tab query does not know how to deal with it. It reports, "Does not recognize '[This_Year]' as a valid name or expression". And I don't know how to get the cross-tab query to recognize it.

[June7]

Not easy to do because of the dynamic nature of the column headers, especially when pivoting on date values.

Filter parameters for CROSSTAB require special handling.

Review http://allenbrowne.com/ser-67.html

I don't think formatting to Short Date will get you want you want. Format function returns a string value which will not sort chronologically. To get the columns to order chronologically, either use the unaltered date value or format it like: Format([Beg_Date], "mm/dd") so the output will be like 08/10, 11/29, etc. and the alpha sorting will appear as chronological.

[WCStarks]

Great. Thank you June7. I set a parameter to [This_Year] as short text. It allowed the query to go and it works. Some of the events are not sorted correctly, as you said they would not. Any suggestion on how can I see just the date and exclude the time in the pivot columns and still get them to sort?

[June7]

I suggested a Format expression.

[WCStarks]

Sorry. That was dumb. After going to the link, I forgot to finish reading your response.

[June7]

And if you want to show month name along with month number, consider: Format(Date(),"mm(mmm)/dd"). If you prefer, use a - in place of /.

[WCStarks]

Thanks. Now I need to figure out how to create a dynamic report based on this query. Reading MS's how to, sounds quite complicated.

[June7]

Yes, it is. There is a link to an example in Allen Browne's article.

[WCStarks]

Has anyone written a generic VBA code to work with any cross tab query, by just supplying the name of the query to process? This cross tab query produces a variable number of columns and column headings.

[June7]

Variable number of columns and changing header names in a CROSSTAB is to be expected because they are derived from data and data changes, especially when pivoting on date values. The Allen Browne link is best reference I know of for examples of handling dynamic CROSSTAB in a report and I suppose even it won't deal with every situation. Yours may be one.

I have resorted to 'temp' table and VBA for process that required rearranging data horizontally. https://forums.aspfree.com/microsoft...ry-322123.html