• Registered: 2024-12-17
  • Posts: 14

Topic: Help creating SQL Report to show data from table1 and linked table2

Good day,

I need help creating a report to show data from main table1 and linked table2

SELECT student.fname, student.lname, student.grade, student.school_year, student.comments, trimesters.class, trimesters.trimester1, trimesters.trimester2, trimesters.trimester3
FROM student

INNER JOIN trimesters ON student.id_trimesters = trimesters.id_student
ORDER BY trimesters.class

When I run the report I don't get any data, just a blank report.

Post's attachments

Attachment icon student data tables layout.jpg 94.8 kb, 75 downloads since 2024-12-17 

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 3,027

Re: Help creating SQL Report to show data from table1 and linked table2

Hi,
It could be a couple of things so it's always best to upload your whole project (delete the .exe file and then .zip the application folder).
But from the screen shot you attached, there seems to be some inconsistencies (mainly joins and redundancies) in your data schema.
Regards,
Derek.

  • Registered: 2024-12-17
  • Posts: 14

Re: Help creating SQL Report to show data from table1 and linked table2

Thank you Derek for your quick response.
.
As per your request, I have attached the my project.
Please let me know what I did wrong.
.
Thanks again.

  • Registered: 2024-12-17
  • Posts: 14

Re: Help creating SQL Report to show data from table1 and linked table2

sorry I forgot to send the project files.

  • Registered: 2024-12-17
  • Posts: 14

Re: Help creating SQL Report to show data from table1 and linked table2

..

  • Registered: 2024-12-17
  • Posts: 14

Re: Help creating SQL Report to show data from table1 and linked table2

Some how the file is not attaching
Please tell me what I could be doing wrong.

Post's attachments

Attachment icon students.zip 355.24 kb, 156 downloads since 2024-12-18 

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 3,027

Re: Help creating SQL Report to show data from table1 and linked table2

Hi
Sorry for the delay - Christmas is getting in the way big_smile
In the attachment is your original application with an amendment to the ''reportsql'';  however, although this retrieves data, it is not selecting the correct record - to fix this, you'd need to add some parameters to the ''reportsql'' selection (and I suspect you'll also need to amend your data schema).
It would probably be easier if you replaced the ''reportsql'' option with the ''report'' option which generates the SQL for you.
I've also attached a slightly different option just to give you an idea of what basic MVD can do;
A).  it uses fewer forms so navigation is simpler and entering data is speeded up.
B).  when you save a record, it stays on the same form - ready for you to enter marks for the next class etc etc (which saves you having to re-enter the student every time).
C)  everything (except for ''comments'')  uses reference tables to ensure data integrity (and again, speeds things up).
D).  the report can either show a single class (when a row in the tablegrid is highlighted) or all classes when no row is highlighted (to de-select a highlighed row, click on the 'Select Report Card' button)..
E).  the selection criteria on the main form make it very easy to compare a student's ''year on year'' performance generally and by specific class;  obviously, with more ''real'' data, it also becomes very simply to see things like who is ''top of the class' (and bottom!) by filtering on year / grade / class which may be of interest.
It's not meant to be a finished version (far from it) - it's more to give you some ideas that you might want to include in any subsequent changes you make.
If anything's not clear, shout out and there is usually someone around who can help out.
Regards,
Derek.

Post's attachments

Attachment icon mtp.zip 714.47 kb, 187 downloads since 2024-12-19 

8 Reply by mtp24 (edited by mtp24 2024-12-19 01:19:41)

  • Registered: 2024-12-17
  • Posts: 14

Re: Help creating SQL Report to show data from table1 and linked table2

Derek Thanks a million! smile
The 2nd choice you gave me worked perfect!

  • Registered: 2024-12-17
  • Posts: 14

Re: Help creating SQL Report to show data from table1 and linked table2

Good day, Derek.

I have another question about this application.
What would be the best way to modify or add more fields to the pupils table?
Also, I want to have a form for that table that I can add, modify and delete full info for each pupil as well as print reports, single student and all students.
Thnaks

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 3,027

Re: Help creating SQL Report to show data from table1 and linked table2

Hi,
It's simply a case of adding more fields to the ''pupils'' table in the data schema;  none of the data that you have already entered will be affected (although as ''good practice'', it's worth taking a copy of the ''sqlite.db'' before starting any work involving changes to the structure).
After that, you can basically create any form to put the extra details on;  sometimes it makes sense to use a separate form for ''extra'' details but often it's just a case of adding the extra fields to an existing form. 
In the attached example, I've simply added them on to the ''editable grid'' used in my previous attachment (because it's quick);  you might prefer to take the more usual ''list form'' / ''maintenance form'' approach to collect all your student details (and this is the approach that I would probably take myself but as I said earlier, the attachments are just quickly knocked-up examples to give you some ideas as to what you can do with MVD).
There is now a ''combobox'' on the student details tabsheet;  leave it empty to run a report listing ALL students or select a student to run a report for a single student.
The idea is not to provide you with a finished solution but to give you something basic that you can use to try out different things and familiarise yourself with some of MVD's many features and options.
Derek.

Post's attachments

Attachment icon students different approach 2.zip 359.59 kb, 163 downloads since 2024-12-19 

  • Registered: 2024-12-17
  • Posts: 14

Re: Help creating SQL Report to show data from table1 and linked table2

Derek Thanks another million! smile
Your ideas are well appreciated.:cool:
I will try as you stated. 
Thanks again and have a wonderful holidays. Of course, that is, if you celebrate it.

12 Reply by mtp24 (edited by mtp24 2024-12-22 02:44:45)

  • Registered: 2024-12-17
  • Posts: 14

Re: Help creating SQL Report to show data from table1 and linked table2

Hello everyone.

Going back to this topic.
I am having an issue with displaying the comment field on the report.
I have checked and compared the comment field everywhere that I think it is being used, but it still not showing up on the report.
Can you give me a hint of what I could be doing wrong.

Also, the Image saved on the record are not showing either.
I get a some letters in place of the image, something that looks like " y0ya " with some dot on top of the y and the a


Thank you.

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 3,027

Re: Help creating SQL Report to show data from table1 and linked table2

Hi,
Both issues are probably easy fixes but you need to upload your project otherwise it's just guesswork as to what the problems could be..
Derek.