I have two tables: checks and invoices (don't we all?):
TABLE INVC (VNO I, VJOBID C(10), VAMT Y)
TABLE CHCK (CNO I, CJOBID C(10), CAMT Y)

I want to compair total billed vs total paid for each JobID.

The following dosn't work:

SELECT VJOBID, SUM( VAMT ), SUM( CAMT ) ;
FROM INVC JOIN CHCK ON VJOBID = CJOBID ;
GROUP BY VJOBID

because of the many to many relationship between the tables.

I can do

SELECT VJOBID, SUM( VAMT ) ;
FROM INVC ;
GROUP BY VJOBID
UNION ;
SELECT KJOBID, SUM( CAMT ) ;
FROM CHCK
GROUP BY KJOBID

but I would like to be able to define a view that can be exported via ODBC so that a user can create crystal report reports. (now we know the real problem!)

^Carl Karsten