How can I pivot a table without using an aggregate function

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?
envirospatialAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MIKESoftware Solutions ConsultantCommented:
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
mlmccCommented:
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
envirospatialAuthor Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

MIKESoftware Solutions ConsultantCommented:
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
MIKESoftware Solutions ConsultantCommented:

Minor correction in example:

select name, amount_engaged,YearField,'Engagements'
from engagements
UNION ALL
select name, amount_paid, year, 'Paid'
from Payments
0
mlmccCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
envirospatial,

" I am using Access and CR."

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

JeffCoachman
0
MIKESoftware Solutions ConsultantCommented:
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
envirospatialAuthor Commented:
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
MIKESoftware Solutions ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
"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
envirospatialAuthor Commented:
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
envirospatialAuthor Commented:
Thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.