HELP!! Need to make one group fwith data from two tables.

[mmwdmr]

I'm sure the solution is an easy one but it's just not coming to me!

I am working with two accts receivable tables. One table has an invoice number and billed transaction detail. The other table also has the invoice number and all the payment transactions applied to that invoice. What I need to do is group the billed detail and the payment detail together by the invoice number.

So, I need transactions that are attached to ROPIT.DOCNUM (which is the invoice number for the billing detail) and the transactions that are attached to RHEADER.NUMBER (the invoice number for the payment detail) to print on the report as one group.

ROPIT.DOCNUM AND RHEADER.NUMBER both represent the invoice number. When a payment comes in (RHEADER) it is applied to the original invoice (ROPIT).

Oh, and this would follow the first group which is the Client's account number. Thanks!!

 

[lbass]

You could link the two tables on the invoice number fields, using a left join from ROPIT to RHEADER. If you have multiple bills for each invoice though, I think you will have repeating values from the payments table.

Another approach would be to use ROPIT in a main report, where you would group on ROPIT.DOCNUM and then add a subreport with the other table. Link the subreport to the main report on the invoice field, and place the subreport in the group header or footer. You would then need to use shared variables if you wanted to do calculations using fields from both tables.

-LB

 

[mmwdmr]

Thanks. I did end up going the subreport route, except I grouped on transaction type (I for invoice, C for credit). I was trying to avoid it because it increases processing time.