• Registered: 2018-12-02
  • Posts: 152

Topic: Create report using data from multiple tables.

Hello!
I need to create a report containing data from multiple tables, all tables, linked to a single table by a common field. I tried the Report command and ReportSql but failed to configure properly. I have attached a smaller project created from the original project for easier understanding. The report needs to include PID, First_Name, Last_Name, OPD_ID, Date, Complaints, Eyes complaining about, Doctor, Diagnosis, Eye related to diagnosis Please help.

Post's attachments

Attachment icon clinic_report_01.zip 330.2 kb, 438 downloads since 2020-09-21 

2 Reply by derek (edited by derek 2020-09-26 16:42:32)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,761

Re: Create report using data from multiple tables.

Hello Eyeman,
I had a quick look at your download but wasn't sure about a couple of things.
1.  Don't you need to have a 'complaint' (aa_03_complaint) before you can offer a diagnosis (aa_04_clinic)?  Yet there is no relationship between the aa_03_complaint and aa_04_clinic tables.
2.  It appears from the data, that for every complaint, there should be a diagnosis (ie a 1:1 relationship).  If that's the case, is there any advantage in having the aa_04_clinics table at all - it could just all be held on aa_03_complaints.
If either 1) or 2) or both of the above is true, then it makes your report pretty simple to write.
But as it stands (without changing your data structure), I think you will need a main report (for the 'patient' and 'OPD' information) and then 2 sub reports (for the 'complaints' and the 'clinics' information).  I've little experience of using FastReport but I think that would entail creating the report datasets by script which is a lot less straightforward.
Just my view - others may see different ways to address the problem.
Derek.

  • Registered: 2018-12-02
  • Posts: 152

Re: Create report using data from multiple tables.

Hello Derek,
Thanks for your insights. I have made the OPD_ID as the main link for the tables aa_03_complaints and_ aa_04_clinics. I might include diagnosis within the aa_03_complaints table, but I would like to include other details like History, Glass Power, Medications, Advice etc linked to each OPD_ID. In the original project, I have created sub-forms for each of this and stored them in separate tables. As you have mentioned, I would need a main report with related sub-reports. How to create the same?
Regards,
Eyeman

Post's attachments

Attachment icon opd_visit.zip 94.56 kb, 430 downloads since 2020-09-27 

  • Registered: 2018-12-02
  • Posts: 152

Re: Create report using data from multiple tables.

Hello,
I  have almost generated the desired report using Report(Sql) option. I'm stuck at one point. I want to include the Select_Eye for the Complaints also. But since Select_Eye is a foreign key to both aa_004_clinics and aa_003_complaints, there appears an error message "ambiguous Select_Eye...".How can the eye of complaint and diagnosis be selected? Do I need separate lookup tables for Complaint_Eye and Diagnosis_Eye?
Regards,
Eyeman

Post's attachments

Attachment icon clinic_report_02.zip 481.66 kb, 508 downloads since 2020-10-04 

  • From: Philippines
  • Registered: 2018-10-23
  • Posts: 429

Re: Create report using data from multiple tables.

eyeman303 wrote:

Hello,
I  have almost generated the desired report using Report(Sql) option. I'm stuck at one point. I want to include the Select_Eye for the Complaints also. But since Select_Eye is a foreign key to both aa_004_clinics and aa_003_complaints, there appears an error message "ambiguous Select_Eye...".How can the eye of complaint and diagnosis be selected? Do I need separate lookup tables for Complaint_Eye and Diagnosis_Eye?
Regards,
Eyeman

I don't have time to check on the details but for the Report SQL, all you have to do to avoid that ambiguous error is to explicitly select each Select_Eye with their corresponding Table. Here's a sample to join lu_004_Select_Eye named as CE and then explicitly select the two Select_Eye Fields that is in same table lu_004_Select_Eye but in different name(CE and E) and joined to separate table clinic and complaints. Hope this gives you an idea

SELECT
V.id, OPD_ID, Date,
First_Name,Last_Name, PID ,

doctor, complaints ,diagnosis , E.Select_Eye as SE_Diagnosis, CE.Select_Eye as SE_Complaints 

FROM                                               
aa_002_opd_visits V                                           
                                    
INNER JOIN    
aa_001_patients P  ON  V.id_aa_001_patients = P.id

INNER JOIN
lu_001_doctors D  ON  V.id_lu_001_doctors = D.id        

INNER JOIN                                                 
aa_004_clinics C ON  C.id_aa_002_opd_visits = V.id

INNER JOIN               
lu_003_diagnosis G ON G.id = C.id_lu_003_diagnosis 

INNER JOIN
lu_004_Select_Eye E ON E.id = C.id_lu_004_Select_Eye

INNER JOIN
aa_003_complaints  M ON   M.id_aa_002_opd_visits = V.id  

INNER JOIN           
lu_004_Select_Eye CE ON CE.id=M.id_lu_004_Select_Eye

INNER JOIN      
lu_002_complaints  LM ON LM.id = M.id_lu_002_complaints 


 where
 V.id = $id
brian

6 Reply by eyeman303 (edited by eyeman303 2020-10-04 09:15:55)

  • Registered: 2018-12-02
  • Posts: 152

Re: Create report using data from multiple tables.

Hi Brian,
It's an eye-opener. Now the desired report is generated. I'm so happy.
Thanks a ton.
Eyeman