Solved

Dynamic Crosstab Report

Posted on 2009-05-18
13
1,001 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
ID: 24414178
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
ID: 24414533
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
ID: 24414752
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24414893
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
ID: 24414971
Yes, that is what the data looks like.  It does not have to be crosstab
0
 
LVL 74

Expert Comment

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

untitled.JPG
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24415177
0
 

Author Comment

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

Expert Comment

by:Jeffrey Coachman
ID: 24416037
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
ID: 24416411
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
ID: 24418510
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
ID: 31582678
Thank you.  Your help is appreciated.
0
 
LVL 74

Expert Comment

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

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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