Reformatting SQL for Report

[Bonediggler1]

Hi-

I have a report with a/r data that includes account, invoice #, transaction type, transaction amount, and check #. Every account has multiple invoices, each invoice can have multiple transactions (of different or the same type), and each transaction which is a payment may have a unique check #. The data basically looks like this:

ACCT  INVOICE  TRANS_TYPE  AMOUNT   CHECK_#
123   4567     CLAIM       1.00     NULL
123   4567     CLAIM       3.00     NULL
123   4567     ADJST       2.00     NULL
123   4567     PAYMENT     1.00     908573
123   4567     PAYMENT     5.00     973246

The report users are now requesting that each invoice has no more than one line. Using the above example, the fields would now have to be arranged as:

ACCT  INVOICE  CLAIM_1  CLAIM_2  ADJST_1  PAYMENT_1  PAYMENT_2 CHECK_1  CHECK_2

I can think of a way to do this that is complicated and would take a really long time. I am wondering if anybody here has a way to do this that is pretty straightforward?

Thank you!!

 

[PWise]

And what do thay want when there are mor then 2 checks Ect?

 

[Bonediggler1]

This was just an example.

Once the conceptual logic is figured out, I would determine a maximum number of transactions per transaction type and number of checks per payment - and use that many fields in the final report, regardless of whether they get populated all the time or not.