(6.5++ to 7.x)
I converted my easy reports and now am doing the "busier" ones. The conversion went
OK but on the report I was work on today, it ran mmmuuuccchhhh slower (even with single pass). Lots of SUM OF this and that.
In reading your thread and the list, it looks like the way to go is make a TEMP table/view and do all or most of the sums/calculations there and not in the report. True?
And, any other report tips are appreciated.
TIA,
Dennis *****
At 08:47 AM 9/23/2004 -0400, you wrote:
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 > >
Dennis Fleming IISCO http://www.TheBestCMMS.com Phone: 570 775-7593 Mobile: 570 351-5290
