[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I pivot a table without using an aggregate function

Posted on 2008-11-18
13
Medium Priority
?
796 Views
Last Modified: 2012-08-13
I have two similar tables - Engagements and Payments.   Engagements has: name, amount engaged, year fields - Payments has: name, amount paid, year.  I would like to have a report that is grouped by name, pivots the year values and shows both the amount engaged and amount paid ( and the difference) in each "cell".  I am using Access and CR.

Any ideas?
0
Comment
Question by:envirospatial
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 17

Expert Comment

by:MIKE
ID: 22991091
Sounds to me like you need to create a MANUAL CROSSTAB in Crystal, ...which means manually creating your crosstab within the report sections of the main report.

You would have to use formulas to align the report to look like a crosstab/pivot.

I would recommend using a standard Crosstab.. but they are for summary only..and will not allow for the DIFFERENCE of the Engaged and Paid amounts. Which is why this needs to built manually.

You can simply SUPPRESS the reporting sections that you do not need so as to create the appears of a crosstab / pivot.

Hope it helps.

M
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22991201
Assuming you link the tables on the name and year fields you will get a recordset with

Name  Year   Engaged  Paid  

You can then create a formula like below and use it in the cross tab.
Engaged - Paid

mlmcc
0
 

Author Comment

by:envirospatial
ID: 22992290
Thanks for the comments CrXIUser2005 and mimcc.  but I am not sure how to proceed.
mimcc:
I tried to link tables as suggested.  The problem is that I have engagements for future years where I do not have payments.  The outcome is that when linked I only get the years that have payment and not all years.

CrXIUser2005:

I am not sure how to create the manual crosstab in Crystal. How would I get each Year as a column header.

Thx
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 17

Expert Comment

by:MIKE
ID: 22995619
Try a UNION ALL sql statement:

select name, amount_engaged,YearField,'Engagements'
from engagements
UNION ALL
select name, amount_paid, year, 'Paid'

Or something like this...you data will then be placed into a SINGLE DATA TABLE...you can then use formula VARIABLES to calculate the data anyway you choose, grouping on the name as you desire.

M
0
 
LVL 17

Expert Comment

by:MIKE
ID: 22995670

Minor correction in example:

select name, amount_engaged,YearField,'Engagements'
from engagements
UNION ALL
select name, amount_paid, year, 'Paid'
from Payments
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 22995909
You can LEFT OUTER JOIN the table

EngagedTable LEFT OUTER JOIN PaymentTable

With a left join you get all the records in the left table and the matching records from the right table

mlmcc
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22996261
envirospatial,

" I am using Access and CR."

OK great, but can you be specific on what program you want a soultion in?

JeffCoachman
0
 
LVL 17

Expert Comment

by:MIKE
ID: 22996620
Just FYI...my recommendations are from the stand-point of using CRYSTAL as the main reporting tool....to query an ACCESS DB.

My syntax is SQL Server...but I don't think it changes all that much in Access.....

?
0
 

Author Comment

by:envirospatial
ID: 22998157
Thanks for the comments.  
Not yet had a chance to try the latest suggestions.  At first glance-  mimcc:  I have tried the left join .. when I look at the pivot, only the dates that are in both tables show up.   CRUserXI2005:  That seems like the approach needed, how the key is setting up the formula..  not sure yet, will try.

To  clarify, Yes Using Access as DB and CR for reporting.. Will post soon.
Thx
0
 
LVL 17

Accepted Solution

by:
MIKE earned 375 total points
ID: 22998187
Again you'll need to GROUP BY NAME and then build some VARIABLE FORMULAS that will calculate and accumulate the totals you need....then display them in the appropriate section, probably a GROUP FOOTER....

Hope it all works out.

M
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22998806
"Yes Using Access as DB and CR for reporting"
OK, but again,...
Which platform do you want the soultion in:Access or Crystal.
Pick ONE please.

Or should I presuume that based on the other Experts posts, that you want a Crystal solution?
;-)

JeffCoachman
0
 

Author Comment

by:envirospatial
ID: 23002465
Thanks for the help.

I have set up the report as CRUserXI2005 has suggested.  After some trial and error with variable formulas, I can format as I wanted.

Cheers

boaq2000:  Sorry for not being clearer.

Thx
0
 

Author Closing Comment

by:envirospatial
ID: 31518060
Thanks for your help.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
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…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

831 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