Dynamic Crosstab Report

I have to create a report that uses a flat table that returns :
Account Name, VisitNumber,  hours, supervisor

I need to create a dynamic crosstab that will return Acct Name, Visit Hours (by visit date), Visit Date (by visit number) in each row and Visit Number as the column headers so that I can create a report that dynamically creates the column headers for visits, based on the number of visits returned in the query.

How do I create the crosstab and return more than one value by visit number?
How do I then automate the dynamic names of the visit numbers in the report header?

Thanks in advance.
dleadsAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
No,

But this should get you what you want:

TRANSFORM First(Format([VisitDate],"mm/dd/yyyy") & ", " & [Hours] & ", " & [Supervisor]) AS Fields
SELECT tblData.Account
FROM tblData
GROUP BY tblData.Account
PIVOT "Visit " & [VisitNum];

Obviously you will substitute your actual Object Names for mine.

Then simply make a Report out of this Query.
With a little bit of work on your own, it should end up looking something like this:

untitled.JPG
0
 
peter57rCommented:
I don't really understand what you are trying to show.
Perhaps if you could create a sample result an XL spreadsheet we would get a clearer picture.

However, the code to handle the creation of reports from crosstab which do not have consistent headings is substantial.
There is an example here:
http://support.microsoft.com/kb/328320

If you can enforce consistent headings by using the Columns property then it would be straightforward.
0
 
dleadsAuthor Commented:
The result should look like this:

Account:             Visit 1                        Visit2                            Visit3                           Visit4              ETC...
                     Date/Hours/Superv   Date/Hours/Superv    Date/Hours/Superv      Date/Hours/Superv
ABC              4/4/09  4    Joe          4/5/09  3   Mike           4/8/09  1  Kelly            5/4/09  4    Mitch
DEFG Inc.      3/4/09  4   Bill            3/8/09  13  Mike          4/1/09  4  Stewart       4/4/09  3   Bob

The number of visits can change
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Jeffrey CoachmanMIS LiasonCommented:
dleads,

While what you are asking probably can be done, it will not be easy.

Does this "have" to be a crosstab query?

A standard Grouped report (That displayed the exact same data, only vertically) would not be acceptable?

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
I always helps if you post soem sample Data.

Does your data look like this:
Account      VisitNum      Hours      Supervisor
Account1      1      4      Mike
Account1      2      7      Joe
Account1      3      4      Sally
Account1      4      7      Mike
Account2      1      7      Joe
Account2      2      5      Sally
Account2      3      8      Mike
Account2      4      8      Joe
Account2      5      5      Sally
Account3      1      5      Mike
Account3      2      8      Joe

Account	VisitNum	Hours	Supervisor
Account1	1	4	Mike
Account1	2	7	Joe
Account1	3	4	Sally
Account1	4	7	Mike
Account2	1	7	Joe
Account2	2	5	Sally
Account2	3	8	Mike
Account2	4	8	Joe
Account2	5	5	Sally
Account3	1	5	Mike
Account3	2	8	Joe

Open in new window

0
 
dleadsAuthor Commented:
Yes, that is what the data looks like.  It does not have to be crosstab
0
 
Jeffrey CoachmanMIS LiasonCommented:
This took me less than 1 minute to create with the Report Wizard:

untitled.JPG
0
 
Jeffrey CoachmanMIS LiasonCommented:
0
 
dleadsAuthor Commented:
Thank you, but it needs to be across in columns, not repeated down the report.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Then use a Pivot table.

Open the sample, then open the table , then Click: View-->Pivot Table View

To built this:
Open your table and then Click: View-->Pivot Table View
Drag the AccountNumber Field to the section called: "Drop Row Fields Here"
Drag the VisitNumber,  to the section called "Drop Column Fields Here"

Then Drag Supervisor, Visit Date, and Hours to the section called:
"Drop Totals or Detail Fields here"

JeffCoachman
Access-EEQ24418251CrossTabPivotT.mdb
untitled.JPG
0
 
dleadsAuthor Commented:
boag2000 - The pivot table example are the results I need, but it needs to be in a report.  Is there a simple way to convert the pivot results to a report recordsource?
0
 
dleadsAuthor Commented:
Thank you.  Your help is appreciated.
0
 
Jeffrey CoachmanMIS LiasonCommented:
... And thank you for an interesting and thought provoking question.
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.