Topic: Report (SQL) Query

Hello All,

I am stuck.

I am trying to write a simple query for Report SQL but I can't seem to get the relationship data between tables to pull through.

Can anyone help me write this simple query? I have attached what I have so far.

Thanks

Post's attachments

Attachment icon Query_Sample.pdf 41.78 kb, 80 downloads since 2020-07-18 

Re: Report (SQL) Query

Hi,
Can you upload your project - the pdf only shows part of the query?
Derek.

Re: Report (SQL) Query

Hi Derek,

What you see in the pdf is all the query that I have written so far. The project that this is connected to is very large and I would rather not attach to the forum. I am sure there is a simple solution to my problem. I just can't see it. Here is more information on my project details.

table names:             column name:

contacts                    bowler_name_id
award_type               award
bowling_weeks         week_num 

I just tried this and I am now getting a syntax error by ","

SELECT

award_recap.date,
award_recap.game1,
award_recap.game2,
award_recap.game3,
award_recap.total,
award_recap.high_game,
award_recap.high_scratch,
bowler_name_ID FROM contacts where contacts.id = bowler_name_ID,
awards FROM award_type WHERE award_type.id = awards,
week_num  FROM bowling_weeks WHERE bowling_weeks.id = week_num

FROM award_recap

Re: Report (SQL) Query

That is normal, you cannot have a “FROM” within a “WHERE” clause without having a select in parenthesis, you need to use UNION to pull the information from other tables plus, you are calling a column which does not exist if we refer to the table and column names you describe: awards in the where clause.... we could better help you if you attached your project with a description of what result you are looking for. There are so many different results you can pull from a simple query depending on how you write it!

5 (edited by tjohnson0473 2020-07-18 20:48:22)

Re: Report (SQL) Query

Thank you for your assistance.

Is it possible for someone to write me a sample query from the table examples that are attached to make the Union between the tables? I can use the report function to create the report, but it only displays one record at a time. If I use the Report SQL and write a simple query, I can get all records to display minus the ones that have a relationship with another table.

Post's attachments

Attachment icon Sample Tables.pdf 44.77 kb, 68 downloads since 2020-07-18 

Re: Report (SQL) Query

Hi,
I'm not sure why you can't use the 'report' function rather than 'reportsql'.
Anyway, please see the attached (report function for all rows, report function for highlighted row and reportsql function).
Hope this helps,
Derek.

Post's attachments

Attachment icon tjohnsonreport.zip 338.89 kb, 109 downloads since 2020-07-19 

Re: Report (SQL) Query

Thank you Derek,

I would rather use the report function, but for some strange reason it will not display any of the relationship data between tables or it will only display the first record only. Unless I am doing something wrong which could be the case. Do you have any idea why this would be?

8 (edited by derek 2020-07-19 15:49:35)

Re: Report (SQL) Query

If all the rows of data display correctly in a tablegrid, then I'm not sure why the 'report' function doesn't work correctly.
If your project is too large, is it possible to take a copy and chop out the unnecessary bits to leave just the part where you can see the reporting problem and then upload that.  Maybe then someone might spot something.
Derek.

Re: Report (SQL) Query

Some reading about JOIN and UNION:

JOIN: https://www.tutorialspoint.com/sqlite/s … _joins.htm

UNION: https://www.tutorialspoint.com/sqlite/s … clause.htm

Re: Report (SQL) Query

Thanks Derek for the example. Does any know how to modify the search my comboBox? I found an example in the forum but I cant seem to get the data to pull through to the report.

SELECT                                   
  payments.date,
  payments.payment,
  contacts.bowler_name_ID,
  payments.membership,
  payments.secratary_fees,
  bowling_weeks.week_num
FROM
  payments
LEFT OUTER JOIN
  contacts on contacts.id = payments.id_contacts
LEFT OUTER JOIN
  bowling_weeks on bowling_weeks.id = payments.id_bowling_weeks
 
WHERE ("bowler_name_ID" LIKE '%{Edit_Search_Value}%')
        AND (  "contacts on contacts.id = payments.id_contacts" = '{ComboBox_Seacrh_ComboBox1}')
               
        ORDER BY "bowler_name_ID" ASC

Re: Report (SQL) Query

Is there anyone that can help with my latest post? I have tried many different scripts but no luck. Once I try to search by ComboBox the information will not display on the report. I have tried using the Report function instead of Report SQL but the information still will not display. Has anyone else come across this before?

12 (edited by derek 2020-08-05 15:26:15)

Re: Report (SQL) Query

Hi,
In the sqlreport code that you've pasted in your search, it refers to '{combobox_SEACRH_combobox1'} - could it be something as simple as a typo (seacrh instead of search?).
Failing that, can you upload your project otherwise it's a bit like shooting in the dark (or in your case, bowling in the dark - LOL!)?
Derek.

Re: Report (SQL) Query

Hi Derek,

I have added a sample project for you to look at. I built the sample on the delete table project you did for me because I am also having an issue with the project trying to run the script for the registration keys that is on the inclosed notepad file. Since there is 2 procedure for OnShow for fmDashboard it gives an error. I have tried to rewrite the script to make it work with no luck. Can you please take a look at it. This is another major function of my project since I need it to expire so a new key can be used to reactivate it.

I set the report to use the "Report" function instead of the script to show that the information on the table grid will not pull. I also tried the search script by name and is still will not work. I am thinking it has something to do with the relationships between the tables.

I can't thank you enough for your help with this. my client starts beta testing the project in a week so I am really excited to get this portion completed.

Post's attachments

Attachment icon deletetables.zipx 282.38 kb, 51 downloads since 2020-08-08 

14 (edited by derek 2020-08-09 14:03:52)

Re: Report (SQL) Query

Hi,
Please remove frmpayments.button1 (save record) - it isn't needed and is clashing with frmpyaments.button3.. 
When you click on the report button (frmpayments.button3), it first checks that you have got the most up to date information.  Part of that check is if there is a 'save' button on the form -  and if there is,  it will action it first before running the report. 
But in this instance, what the 'save' button ends up doing is adding your search criteria as a new record - this will either be a blank record (because you run the report with no search criteria) or a record with whatever search criteria you entered. 
The report then runs using the record that has just been saved as its criteria, which is why you weren't seeing the results you were expecting.
If you do this, when you exit the report, you'll see the newly created blank record (or record with your selection criteria) has been added to frmpayments.tablegrid1.
So, delete frmpayments.button1 and you should be okay.  Your report and your relationships were never the problem.
One other thing to note is that you have duplicate rows of data in frmpayments.tablegrid1 (eg, week2 / 08.08.2020 / erik / team3 / 15.00). 
If you run the report with no search criteria, it displays 14 rows of data (ie without duplicates) whereas there are 17 rows of data in frmpayments.tablegrid1. 
This may be just because it's test data and would never happen.  But to safeguard against it, you could add the payments.id to the fields to be passed through to the report to ensure uniqueness (you don't need to actually display payments.id on the report itself).
I hope that's clarified things and not confused them!
I've also added your 'registration key' code back into the script so that it now compiles. A couple of things needed changing:
Line 81:  you had 16 keys defined (0-15) but only 15 defined in the array
Line 94:  you are using a longer key than in EHWagner's example (Hi EHW) so your key length should be 1,29 instead of 1,19)
Line 98:  the duration in days is held in position 30,3 instead of 20,3)..
Hope this moves you forward.
Derek.

Post's attachments

Attachment icon deletetables1.zip 351.57 kb, 68 downloads since 2020-08-09 

Re: Report (SQL) Query

Thanks, Derek

That makes a lot of sense now that you mentioned it.

I appreciate all of your help.

16 (edited by agusecc 2020-10-30 07:38:51)

Re: Report (SQL) Query

please help me..
how do i write script in report sql, if i put variable datetimepicker1 and datetimepicker2 ?
thank you

Post's attachments

Attachment icon variable on report Sql.rar 298.94 kb, 23 downloads since 2020-10-29