Solved

Dynamic Crosstab Report

Posted on 2009-05-18
13
993 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:dleads
  • 7
  • 5
13 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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
 

Author Comment

by:dleads
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:dleads
Comment Utility
Yes, that is what the data looks like.  It does not have to be crosstab
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
This took me less than 1 minute to create with the Report Wizard:

untitled.JPG
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
0
 

Author Comment

by:dleads
Comment Utility
Thank you, but it needs to be across in columns, not repeated down the report.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 

Author Comment

by:dleads
Comment Utility
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:dleads
Comment Utility
Thank you.  Your help is appreciated.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
... And thank you for an interesting and thought provoking question.
;-)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now