John Create a view like this (I threw in a few extra columns since you said there was lots of info you need from Transactions and from Doc_Detail):
CREATE VIEW TransDoc (Transaction#,Invoice#,Other1,Other2,Doc_ID, DocCol1,DocCol2) AS + SELECT T1.Transaction#, T1.Invoice#, T1.Other1, T1.Other2,T2.Doc_ID,T3.DocCol1,T3.DocCol2 + FROM Transactions T1, Doc_Link T2, Doc_Detail T3 WHERE T1.Transaction# = T2.Transaction# AND T2.Doc_ID = T3.DOC_ID + UNION ALL + SELECT Transaction#,Invoice#,Other1,Other2, NULL,NULL,NULL FROM Transactions WHERE T1.Transaction# NOT IN (SELECT Transaction# from Doc_Link) This is similar to Dawn's idea, but eliminates the need for the intermediate view. Base your report on the view. The data from the doc_Detail table OR NULL can be printed in the detail section of the report, so no other special report techniques should be needed. David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "John Engwer" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> Sent: Wednesday, September 22, 2004 5:04 PM Subject: [RBG7-L] - Report question > I am trying to create a report that pulls information from three tables. A > brief summary follows: > > Table 1... Purchase table > Contains purchase information. Each purchase record has a unique > TRANSACTION#. Usually multiple TRANSACTION#s for each INVOICE#. > IVVOICE#,TRANSACTION# and many other fields relation to the purchase > > Table 2... Doc_Link table > Contains a list of doctor IDs that are associated with some of the > TRANSACTION#s. Most of the purchases do not have do not have a > corresponding entry in the Doc_Link table. > IVVOICE#,TRANSACTION#, DOC_ID > > Table 3... Doc_Detail > Contains detail information for each doctor including unique ID (DOC_ID) > > I want to use a report to display the purchase detail including a list of > any doctors that are related to the purchase. I have not been able to find > a way to use a view to select the data because there is not an entry in the > Doc_Link table for each purchase record/TRANSATION#. > > Any suggestions on how to solve this problem? I know that I could add a > record to the Doc_Link table for all transactions but 90% of the purchases > do not have doctors associated with them (about 100k records). > > John > >
