John If I understand you correctly, I think you could use an outer join to do what you want. Create a view using table 2 and table 3 linking by doc_id first. Then create a view using table 1 and the view you just created with an outer join. (select...from table1 left outer join viewname on table1.linkingcolumn = viewname.linkingcolumn) Your linking column would be probably transaction# The view would list a doc_id with all necessary info if there was one, and NULLs if there wasn't. (Look at OUTER JOIN in the help for examples to see if I understand you right.) Dawn
-----Original Message----- From: John Engwer [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 22, 2004 5:05 PM To: RBG7-L Mailing List 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
