My Visual Database — Create report using data from multiple tables.

Re: Create report using data from multiple tables.

null@example.com (eyeman303) — Sun, 04 Oct 2020 09:15:20 +0000

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

Re: Create report using data from multiple tables.

null@example.com (brian.zaballa) — Sun, 04 Oct 2020 08:21:09 +0000

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

Re: Create report using data from multiple tables.

null@example.com (eyeman303) — Sun, 04 Oct 2020 07:39:34 +0000

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

Re: Create report using data from multiple tables.

null@example.com (eyeman303) — Sun, 27 Sep 2020 18:44:10 +0000

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

Re: Create report using data from multiple tables.

null@example.com (derek) — Sat, 26 Sep 2020 16:40:21 +0000

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.

Create report using data from multiple tables.

null@example.com (eyeman303) — Mon, 21 Sep 2020 17:18:37 +0000

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.