RE: Crosstab Reports

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"NoviceAccessUser-Melanie" wrote:

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.

"KARL DEWEY" wrote:

Roger Dodger.
If you post your crosstab SQL I will put it together for you.
--
KARL DEWEY
Build a little - Test a little


"NoviceAccessUser-Melanie" wrote:

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?

"KARL DEWEY" wrote:

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


"NoviceAccessUser-Melanie" wrote:

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.

.

Relevant Pages

  • RE: Crosstab Reports
    ... Karl DEWEY" wrote: ... GROUP BY [qryTaxCreditsUNION ALL].SponsorName, [qryTaxCreditsUNION ... I wasn't sure if I needed to create a query including this table. ... I run the crosstab query which has the fields that I ...
    (microsoft.public.access.reports)
  • RE: Crosstab Reports
    ... I built a huge Ind/Deal query that displays shows ... Indicators listed in qryInd for all the deals in qryTaxCreditsUnion. ... "KARL DEWEY" wrote: ... I run the crosstab query which has the fields that I ...
    (microsoft.public.access.reports)
  • RE: Crosstab Reports
    ... Karl, Here you go.. ... GROUP BY [qryTaxCreditsUNION ALL].SponsorName, [qryTaxCreditsUNION ... The crosstab query is built from a Union query of 5-6 select ... I run the crosstab query which has the fields that I ...
    (microsoft.public.access.reports)
  • RE: Crosstab Reports
    ... This should get you closer but I had to use a query and not the Ind table. ... FROM [qryTaxCreditsUNION ALL] ... "KARL DEWEY" wrote: ...
    (microsoft.public.access.reports)
  • Re: Dynamic Reporting based on Parameterized Crosstab Query
    ... The tek-tips solution does display the actual date on the report. ... charge date charged hours are associated with. ... on the first 3 rowwise fields of the crosstab query. ...
    (microsoft.public.access.reports)