?
Solved

Dynamic Crosstab Report

Posted on 2009-05-18
13
Medium Priority
?
1,005 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Suggested Courses

770 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