Ok, I'll try my best to explain this clearly.  I'm using Great Plains SQL tables.  I have three tables:

Table 1 (RM30201) has a record of all invoices and what check amounts were applied to them.

Table 2 (SOP30200) is a sales history table, which lists invoices and there details, but not the information on the individual line items on the invoice.  In other words, it would show the ship date, and document total, but not the different line items and there quantities, etc.

Table 3 (SOP30300) is a sales history line item table.  It lists the details on the line items for each invoice.

My problem is that Table 1 is my starting point and sometimes has more than one record for an invoice.   This is an accounting issue that cannot be overcome, sometimes we just get multiple checks for the same invoice.

Table 2 only has one record for each invoice but needs to be joined to Table 3 which will have a record for each item on its invoice.  Because Table 1 has multiple records I am getting duplicates of the sop tables. 

If Table 1 only had 1 record for each invoice (which showed the sum of check amounts applied to the invoice) I would be fine.  How can I get around this?  Is there, for instance, a way to take Table 1 and from it create a new table that would sum multiple entries and provide a distinct column for invoice number and a column for the total check amounts applied?

There is no other table with the data I need.  Here is my CR command. 

select
   *
from
   rm30201
inner join
  sop30200
on
  rm30201.aptodcnm=sop30200.sopnumbe
left join
  sop30300
on
  sop30200.sopnumbe=sop30300.sopnumbe
 
 

Or...you could start with table 2 and join it to table 3...this is the order and the detail for the order, you could then do a left join to table 1, the payments, which will still give you multiple lines...so doing what I hate...join 2 and 3 and call a subreport for table 1. 

This worked

select
  *
from
(select
  aptodcnm, sum(actualapplytoamount) as totalapplyamount
from
  rm30201
group by
  aptodcnm)
as
  rm30201
inner join
  sop30200
on
  rm30201.aptodcnm=sop30200.sopnumbe
left join
  sop30300
on
  sop30200.sopnumbe=sop30300.sopnumbe