PC Review

Thread Tools Rate Thread

RE: Crosstab Report

Duane Hookom
Posts: n/a
      10th May 2010
There is a solution for dynamic crosstab reporting at
http://www.rogersaccesslibrary.com/f...b_topic11.html. This
solution basically creates an alias for each of the column heading values and
transforms them into A, B, C, D, E, ...

If your TrainDocName values are fairly static and limited, you might get by
with setting the Column Headings property of the crosstab query.

Duane Hookom
Microsoft Access MVP

"gngsquared" wrote:

> I searched, and tried to utilize the answers I found, but I don’t have the
> skills to make them work.
> I have the crosstab query shown below which works fine when the results are
> in datasheet view:
> TRANSFORM Max(DetailTbl.DetailID) AS MaxOfDetailID
> SELECT EmpTbl.EmpNo, EmpTbl.Lname, EmpTbl.Fname
> FROM TrainDocTbl INNER JOIN (EmpTbl INNER JOIN DetailTbl ON EmpTbl.EmpID =
> DetailTbl.EmpID) ON TrainDocTbl.TrainDocID = DetailTbl.TrainDocID
> WHERE (((TrainDocTbl.SelectRec)=On) AND ((EmpTbl.TermDate) Is Null))
> GROUP BY DetailTbl.EmpID, EmpTbl.EmpNo, EmpTbl.Lname, EmpTbl.Fname
> ORDER BY EmpTbl.Lname, EmpTbl.Fname, TrainDocTbl.TrainDocName
> PIVOT TrainDocTbl.TrainDocName;
> [SelectRec] is a Y/N field. There is a form with a listbox that allows the
> user to check which qualifications meet the customer’s needs and then the
> query runs to provide the results. There are currently 15 qualifications to
> choose from, but I’m sure this number will expand as time goes on.
> The required qualifications may change and the report needs to change the
> column headings to reflect this and that is the problem. If I design the
> report for qualifications A, B, and C when the user asks for E, H, and J the
> report gags.
> Additionally, I have formatted the text box on the report to show either YES
> or – by using the following expression: =IIf([fieldname] Is Not Null,"YES","--
> ")
> Although this works, I’m sure there is a better way of doing it.
> .

Reply With Quote

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Crosstab query - want report to look like crosstab alison.justice Microsoft Access Queries 1 31st May 2009 05:35 AM
To Crosstab or not to crosstab OMS Microsoft Access Queries 12 31st Oct 2006 06:58 PM
Crosstab form based on Crosstab query? Penny Microsoft Access Forms 1 31st Mar 2006 07:36 PM
Crosstab query with criteria, dynamic columns and crosstab report question joshblair Microsoft Access Queries 5 13th Jan 2006 10:34 PM
To crosstab or not to crosstab ... That is the question Tom Microsoft Access Queries 1 9th Sep 2003 01:32 AM




All times are GMT +1. The time now is 06:30 AM.