Easy report? How to report on a record, skip, report, etc.

  • Thread starter Thread starter Gdareos
  • Start date Start date
G

Gdareos

I have a totals query that looks like this:

SegmentID LandCost ImpvCost Damages Relo Incidental Demolition
001 $ amt $amt $amt $amt $amt 002
$ amt $amt $amt $amt $amt 003 $
amt $amt $amt $amt $amt 004 $ amt
$amt $amt $amt $amt 005 $ amt
$amt $amt $amt $amt 006 $ amt
$amt $amt $amt $amt 007 $ amt
$amt $amt $amt $amt 008 $ amt
$amt $amt $amt $amt 009 $ amt
$amt $amt $amt $amt 010 $ amt
$amt $amt $amt $amt 011 $ amt
$amt $amt $amt $amt 012 $ amt
$amt $amt $amt $amt
So, I have all the data required, however I have been told that I
**must** write a report that presents the data with the column
headings across the top, with the totals per cost category as row
headings like this:

Seg001 Seg002 Seg003 Seg004 Seg005 Seg006, etc.
LandCost $amt $amt $amt $amt $amt $amt etc
ImpvCost $amt $amt $amt $amt $amt $amt etc.,
Damages $amt $amt $amt $amt $amt $amt etc.,
Relo $amt $amt $amt $amt $amt $amtetc.,
Incidental $amt $amt $amt $amt $amt $amt etc.,
Demolition $amt $amt $amt $amt $amt $amt etc.,

I have read through all I could get my hands on, especially Crosstab
queries, and I cannot figure out how to do this, and of course compute
totals per segment, and totals per cost category. I also tried to
create an Excel pivot table, but that was a disaster.

Is this possible and I think I'm close, because my totals query has
the right data, I just don't know how to write a report this way.

************************************************************************
I am thinking that since I have all the data I need, can I report on
one record, print its values, and then skip to the next record, until
I get through all the records, and do the grand totals? I just don't
know how to do that either.
*************************************************************************

Any help would be **extremely** appreciated!

Thanks,
George
 
I think you can get the desired results by first normalizing your totals
query with a union query:
SELECT SegmentID, "LandCost" as CostType, LandCost as CostAmt
FROM qryTotals
UNION ALL
SELECT SegmentID, "ImpvCost", ImpvCost
FROM qryTotals
UNION ALL
SELECT SegmentID, "Damages", Damages
FROM qryTotals
UNION ALL
---etc---
SELECT SegmentID, "Demolition", Demolition
FROM qryTotals;

Then create a crosstab query with CostType as the Row Heading, "Seg" &
SegmentID as the Column Heading, and Sum of CostAmt as the value.
 
Back
Top