?
Solved

Dynamic Crosstab Report

Posted on 2009-05-18
13
Medium Priority
?
1,009 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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 …

830 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