John,

There may be a way with one SELECT, but here's a 2-pass approach which will be easy to understand:

*!* Get one CURSOR with everything
SELECT Invoices.InvNum, ;
  "Parts" AS RecordType, Parts.PartNo, Parts.TotalCost ;
  FROM Invoices INNER JOIN Parts ;
  ON Invoices.InvNum = Parts.InvNum ;
  UNION ;
  (SELECT Invoices.InvNum, ;
  "Labor" AS RecordType, Labor.EmpId, Labor.TotalCost ;
  FROM Invoices INNER JOIN Labor ;
  ON Invoices.InvNum = Labor.InvNum ;
  UNION ;
  (SELECT Invlices.InvNum, ;
  "Fees " AS RecordType, Fees.FeeType, Fees.TotalCost ;
  FROM Invoices INNER JOIN Fees ;
  ON Invoices.InvNum = Fees.InvNum)) ;
  ORDER BY 1, 2, 3

Note that I use column numbers for the order. With a UNION all fields must be of the same type and size. Use padding and conversion to make it work.

Then, to get your totals, just SUM. This will also make a nice report as you can see.




>I need to get an invoice total. The invoice line items are in 3 files, each of which may have any number of records for a given invoice. The Tables are Labor, Parts, and Fees. The fields I need to total are Labor.LSum, Parts.PSum, Parts.PTax and Fees.FSum.
>
>Is it possible to get a single sum with a SQL Select or even an accurate sum of each of the four fields in one record?
>
>If there are 2 records in each table for a given invoice, everything I try returns 6 times one of the records for each field being summed.