This should get you closer but I had to use a query and not the Ind table.
IndSort ---
SELECT [qryTaxCreditsUNION ALL].Fund, [qryTaxCreditsUNION ALL].SponsorName,
[qryTaxCreditsUNION ALL].Ind
FROM [qryTaxCreditsUNION ALL]
GROUP BY [qryTaxCreditsUNION ALL].Fund, [qryTaxCreditsUNION
ALL].SponsorName, [qryTaxCreditsUNION ALL].Ind;
TRANSFORM Sum([qryTaxCreditsUNION ALL].TCAmount) AS FirstOfTCAmount1
SELECT IndSort.Fund, IndSort.SponsorName, IndSort.Ind
FROM IndSort LEFT JOIN [qryTaxCreditsUNION ALL] ON IndSort.Ind =
[qryTaxCreditsUNION ALL].Ind
GROUP BY IndSort.Fund, IndSort.SponsorName, IndSort.Ind
ORDER BY IndSort.Fund, IndSort.SponsorName, IndSort.Ind
PIVOT IIf([YearID] Is Null,"",[YearID]);
--
KARL DEWEY
Build a little - Test a little
NoviceAccessUser-Melanie said:
Karl, Here's a snippet of the output:
Sponsor Fund Ind
Year Amouht
Actual-Dis
Actual-Loss
Discount-HTC
Discount-LIHTC
Original Proforma-HTC
Original Proforma-LIHTC
CEF CEF2000 Actual-LIHTC Year1
CEF CEF2000 Actual-LIHTC Year2
CEF CEF2000 Actual-HTC Year1
CEF CEF2000 Original proforma-LIHTC Year1
Dela DEF1000 Actual-LIHTC Year1
Dela DEF1000 Actual-LIHTC Year2
Dela DEF1000 Actual-HTC Year1
Dela DEF1000 Original proforma-LIHTC Year1
and it goes on for all the Funds in the database.
I was hoping for the report to look like this:
FUND1
SPONSOR1 Year1 Year2
Year3
Actual-LIHTC 100 100 100
Actual-HTC 10 10
10
Original Proforma-LIHTC 1000 1000 1000
Original Proforma-HTC 100 100 100
FUND2
SPONSOR2 Year1 Year2
Year3
Actual-LIHTC 100 100 100
Actual-HTC
Original Proforma-LIHTC 1000 1000 1000
Original Proforma-HTC
Where the Original Proforma-HTC line will still appear on the report even if
there is no data.
Hope the visual helps. I really appreciate your help.
KARL DEWEY said:
I have been guessing on this. Show me what it is doing right and then what
it is not doing so I can better understand. Post sample data results.
--
KARL DEWEY
Build a little - Test a little
:
Karl, I see where you used the Ind table. I see too where the Left join
property tells it to display all the records in the Indicator table even if
it doesn't match anything in the Big table. But when I run the query, there
is only one set of records that shows all the Indicators. For the rest of the
deals, they only displays the Indicator and row where there is an actual
value. I was hoping it would display a set of all the Indicators per deal
regardless of whether there is a record or not. Just the labels for the rows
would show.
It seems that it needs another join to create rows for the details rows that
do not exist for the Indicators. Am I going down the wrong track with this
thinking?
Karl DEWEY" wrote:
If your Ind table has all then another query is not necessary. See how I
used that table.
TRANSFORM First([qryTaxCreditsUNION ALL].TCAmount) AS FirstOfTCAmount1
SELECT [qryTaxCreditsUNION ALL].SponsorName, [qryTaxCreditsUNION ALL].Fund,
Ind.Ind, [qryTaxCreditsUNION ALL].Indsort2, [qryTaxCreditsUNION
ALL].DealExpr1, [qryTaxCreditsUNION ALL].DealID
FROM Ind LEFT JOIN [qryTaxCreditsUNION ALL] ON Ind.Ind = [qryTaxCreditsUNION
ALL].Ind
GROUP BY [qryTaxCreditsUNION ALL].SponsorName, [qryTaxCreditsUNION
ALL].Fund, Ind.Ind, [qryTaxCreditsUNION ALL].Indsort2, [qryTaxCreditsUNION
ALL].DealExpr1, [qryTaxCreditsUNION ALL].DealID
ORDER BY [qryTaxCreditsUNION ALL].SponsorName, [qryTaxCreditsUNION
ALL].Fund, [qryTaxCreditsUNION ALL].Indsort2
PIVOT [qryTaxCreditsUNION ALL].YearID;
--
KARL DEWEY
Build a little - Test a little
:
Kar, I also have an Ind table with the following fields: Ind, InsSort2,
IndSort3.
I wasn't sure if I needed to create a query including this table.
:
Roger Dodger.
If you post your crosstab SQL I will put it together for you.
--
KARL DEWEY
Build a little - Test a little
:
Karl, I'm not quite sure how to get the totals query. Let me back track a
little bit more. The crosstab query is built from a Union query of 5-6 select
queries for each 'Ind'.Then I create the Union query to put them all in 1
table. From that table, I run the crosstab query which has the fields that I
have listed from my original posting. Do I run a Totals query on the Union
query that has all the combined entries?
:
Create a Totals query on the 'Ind' so it will list all. In the design view
of your crosstab query add the totals and left join it to the other table on
field Ind. Use the Ind field of the totals query as the row heading to
display all Ind's.
--
KARL DEWEY
Build a little - Test a little
:
I am trying to create a Crosstab report where I would like the detail row
headings to be the same for all my groupings. I found the Help About for
'Creating Crosstab Reports' but the last 2 steps are very unclear to me. My
crosstab query looks like this:
Field Total Crosstab Sort
Sponsor Group By Row Heading Asc
Fund Group By Row Heading Asc
Ind Group By Row Heading
YearID Group By Column Heading
Deal Group By Row Heading
Amount First Value
In a normal report, Detail lines for each Ind will appear only if there is
an actual value for that line. I would like to force the label for each Ind
to appear on the report even if there is not value.
The way I understand the 'Create a Crosstab report' Help screen, by
placing the labels in page header this would force those labels to display.
It doesn't sound right to me. I know I am completely reading this wrong.
Can someone please, please help me and tell me where I am going wrong. I
really appreciate the help.
Regards.
