Multiple Passes for Report

[PieterC]

I am trying to convert a query into a report.
Query Example

FileNo Transaction Coverage Amount
1 300 10 5.00
1 300 20 6.00
1 300 30 8.00
2 300 10 10.00
2 300 30 2.00

Report Format Wanted

FileNo Combo1 Combo2 Combo2
1 5.00 6.00 8.00
2 10.00 0 2.00

I am currently getting:

1 5 0 0
1 0 6 0
1 0 0 8
2 10 0 0
2 0 0 2

What do I need to do?

 

[YFS DBA]

Pieter;

Use a cross tab query, with "FileNo" as a row heading, "Coverage" as a
column heading, then sum on the "Amount".

Here's the SQL code, just change "Table1" to whatever your source table or
query is named.

*************************************

TRANSFORM Sum(Table1.Amount) AS SumOfAmount
SELECT Table1.FileNo
FROM Table1
GROUP BY Table1.FileNo
PIVOT Table1.Coverage;

*************************************

HTH

Scott